ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] Ãö©ó Find¥Îªk

½Ð°Ý VBA "Find" ¥Îªk

§Ú·Q°ÝFindªº¥Îªk,
¦³¥ý³]©wprofit ªºrange

  A= Profit.Fine(5.5555,  , xl value,2).Row
·Q­n¦bprofit §ä5.5555 ³o­Ó­È, µM«áŪ¨ú¸ÓÄæ¦ìªº Row address

°ÝÃD¤@: xlvalue«á­±¨º¤@¶µ(2)¬O«ü¦¬´M±o¤p¼ÆÂI¦ì¼Æ¶Ü?
      1.¬O5.5555¥L¬O¥H 5.55¥h§ä¬Û¦Pªº­È
      2. ÁÙ¬O, §äProfit range¸Ì ¤p¼ÆÂI2¦ìªº­È


°ÝÃD¤G:
    ¦pªG Profit ¸Ìªº¤p¼Æ¦ì¼Æ¬OµL½aªº, ¦p¦ó¥Îfind§ä¨ì¬Û¦Pªº­È©O?

°ÝÃD¤T;
  §Q¥Îfind ¨Ó§ä­È ¥i¥Hºë·Ç¦a§ä¨ì¶Ü
­Y·Q­n§äªº­È5.5   ¦¬´M½d³òªº­È 5.55,  5.555,  5.5555,  5.5
¥i¥Hª½±µ§ä¨ì5.5 ¦Ó¤£¬O5.55555

°ÝÃD¥|:
·í­n¦¬´M½d³ò¸Ìªº­È¤p¼Æ¦ì¼Æ«Ü¦h®É, §Ú­ÌÅý¥¦¥H2¦ì¤p¼Æ§e²{
¨Ò¦p 5.234= 5.23

·í¥hfind¸Ó­È®É, ¥LŪ¨úªº¬O5.23(§ó§ï§e²{¤è¦¡¤§«á) ÁÙ¬O ­ì¥ý5.234©O?

Find ¥Îªk.rar (10.16 KB)

TOP

§ä¾ã­Ó¼Æ¦r(5.55)ªº¤èªk:
   Set b = profit.Find(5.55, , xlValues, xlWhole)
§ä¥H5.55"¬°­º" ªº¤èªk (¦p5.55, 5.55001, 5.552,5.55999....)
   Set b = profit.Find(5.55, , xlValues, xlpart)
(»P³o­Ó¤@¼Ë    Set b = profit.Find(5.55, , xlValues)

¯d·Nexcel¬O¥H"¤å¦r"ªº¤è¦¡§ä, ¤£¬O¥H¼Æ¦r¤j¤Ö¨Ó§ä
À´±oµo°Ý,µª®×´N·|¦b¨ä¤¤

¤µ¤éの¤@¬íは  ©ú¤éにない
http://kimbalko-chi.blogspot.com
http://kimbalko.blogspot.com

TOP

¦^´_ 2# kimbal


    ®¦~ ÁÂÁÂ

TOP

[µo°Ý] Ãö©ó Find¥Îªk

½Ð¦U¦ìÀ°¦£, ¤£ª¾¹D­þÃä¥X°ÝÃD
Sub test()

Dim Myrange As Range
Set Myrange = Range(Cells(6, 2), Cells(8, 2))

Cells(1, 1) = Myrange.Find(Cells(3, 2), , xlValues, xlwhole).Row
Cells(2, 1) = Myrange.Find(Cells(3, 2), , xlValues,xlwhole).Column

End Sub

§Ú·Q­n§ä¨ì¸Ó­ÈªºÄæ¦ì¸ê®Æ«oµLªk
'¿ù»~Åã¥Ü: '¨S¦³³]©wª«¥óÅܼƩÎwith °Ï¶ôÅܼÆ

test.rar (11.33 KB)

¦^´_ 1# Helain
  1. Sub Ex()
  2.     Dim Myrange As Range, Rng As Range
  3.     Set Myrange = Range(Cells(6, 2), Cells(8, 4))
  4.     Set Rng = Myrange.Find(Cells(3, 2), , xlValues, xlWhole)
  5.     If Not Rng Is Nothing Then
  6.         Cells(1, 1) = Rng.Row
  7.         Cells(2, 1) = Rng.Column
  8.     End If
  9. End Sub
  10. '''''''''''''''''''''''''''''''''''
  11. Sub test()
  12. Dim Myrange As Range
  13. 'Set Myrange = Range(Cells(6, 2), Cells(8, 2))  '³o¸Ìªº Cells(8, 2) ¬O B8
  14. Set Myrange = Range(Cells(6, 2), Cells(8, 4))   '³o¸Ìªº Cells(8, 4) ¬O D8
  15. '´M§ä¤£¨ì   ¿ù»~Åã¥Ü: '¨S¦³³]©wª«¥óÅܼƩÎwith °Ï¶ôÅܼÆ
  16. Cells(1, 1) = Myrange.Find(Cells(3, 2), , xlValues, xlWhole).Row
  17. Cells(2, 1) = Myrange.Find(Cells(3, 2), , xlValues, xlWhole).Column
  18. End Sub
½Æ»s¥N½X

TOP

§Ú·Q°Ý, «á¨Ó§Ú³o¼Ë°µÁÙ¬O¤£«¬
¥X²{"°}¦C¯Á¤Þ¶W¥X½d³ò" ¬O¤°»ò·N«ä©O???

Find.rar (12.33 KB)

TOP

¦^´_ 6# Helain

°Ñ¼Æ¿ù»~
LookInªº°Ñ¼Æ xlValues
¦ý¬O¡A§Aªº¼Æ­È¬Oª½±µKEY_INªº
LookIn°Ñ¼ÆÀ³¨Ï¥ÎxlFormulas¤~¯à·j´Mªº¨ì
³q±`¹³³o¼Ëªº·j´M·|¬Ù²¤ LookIn¤Þ¼Æ
ª½±µ¨Ï¥ÎLookAt¤Þ¼Æ¡A¨Ï¥ÎxlWhole¨Ó·j´M§¹¥þ²Å¦Xªº¤º®e

§A¥i¸ÕµÛ¾Þ§@½s¿è/´M§ä¥\¯à¡A§âµ{¦¡¿ý»s¤U¨Ó
¥h¤ñ¸û·j´M±ø¥ó¤£¦P¡A³y¦¨¬Æ»ò¤£¦Pµ²ªG
¥¼©R¦W.png
2011-11-9 22:10
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-10 08:22 ½s¿è

¦^´_ 6# Helain

½Ð¥ô·N¿é¤J "aaaaaaa"
RMax_Row = Cells.Find("aa", , xlValues, xlPart).Row
RMax_Row = Cells.Find("aa", , xlFormulas, xlPart).Row                  ¦³§ä¨ì:³o¨âµ{¦¡½X¨S¿ù»~

RMax_Row = Cells.Find("aa", , xlValues, xlWhole).Row
RMax_Row = Cells.Find("aa", , xlFormulas, xlWhole).Row           ¨S¦³§ä¨ì:³o¨âµ{¦¡½X·|¿ù»~    °}¦C¯Á¤Þ¶W¥X½d³ò

½Ð¤£­n¥Î¤W­±ªº»yªk ¥¿½Tªº¼gªk¦p¤U
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)
Set R_Max = R_Profit.Find(What:=Rmax,LookIn:= xlFormulas,LookAt:= xlWhole)
Set R_Max = R_Profit.Find(LookIn:=xlFormulas, LookAt:=xlWhole, What:=Rmax)  
¥þ³¡¦³«ü©ú°Ñ¼Æ¥i¥ô·N©ñ¸m°Ñ¼Æªº¦ì¸m ¦ý¤£¥i¥H °ÑÂøµÛ¦³«ü©ú°Ñ¼Æ ¤Î¨S«ü©úªº°Ñ¼Æ

Set R_Max = R_Profit.Find(Rmax, ,  xlValues, xlWhole)  
Set R_Max = R_Profit.Find(Rmax, , xlFormulas, xlWhole)

¦p½T©w ¥ý«eªº·j´M¤£¬Oµù¸Ñ   LookIn:=xlComments ¥i¥Î
Set R_Max = R_Profit.Find(Rmax, ,  , xlWhole)
    If Not R_Max Is Nothing Then
        R_MaxRow = R_Max.Row
        R_Maxcolumn = R_Max.Column
    End If

TOP

§Ú­«·s¸Õ¤F¤@¤U, ÁÙ¬O¤£¦æ­C!!
§Ú·Q­n§ä¥X¨ä¤¤¬Y¤@½d³ò¸Ìªº³Ì¤j­È,
¦A¦^¥h­ì¨Ó½d³ò§ä¥X¨º­Ó³Ì¤j­ÈªºÄæ¦ì
¥i¬O «o§ä¤£¨ì

test_3.rar (10.89 KB)

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2011-11-10 14:10 ½s¿è

¦^´_ 9# Helain
½ÐÀˬd¤@¤U»yªkªº²Å¸¹
»yªk¥¿½T®É ²Ä1­Ó¦r¥À·|¬O¤j¼gªº      Row = QrF.Row   
¤£«Øij ¥ÎÃöÁä¦r°µÅܼƠ Row,Column ...
  1. If Not QrF Is Nothing Then
  2.         Row = QrF.Row
  3.         Column = QrF.Column
  4.     End If
½Æ»s¥N½X

TOP

        ÀR«ä¦Û¦b : ¡i»X½ªªº¦Û¥Ñ¡j¤H±`¦b¤°»ò³£¥i¥H¦Û¥Ñ¦Û¦bªº®É­Ô¡A«o³Q³oºØÀH¤ß©Ò±ýªº¦Û¥Ñ»X½ª¡AµêÂY®É¥ú¦Ó²@µLıª¾¡C
ªð¦^¦Cªí ¤W¤@¥DÃD