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

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

[µ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)

¦^´_ 5# GBKEE


    http://forum.twbts.com/thread-9780-1-1.html G¤j¥i¥HÀ°§Ú¤@¤U¶Ü??

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¼Ëªº¸ÜÁÙ­n±N5.55¥H«_¸¹¬A°_¨Ó¶Ü"5.55"

TOP

¦^´_ 15# Helain
¦pªG»¡ range ¸Ì­±ªº­È­è¦n¦³¼Æ­Ó¬Û¦Pªº, ¨ºfind ¥i¥HŪ¶Ü?¬OÀH«KŪ¨ú¨ä¤¤¤@­Ó?
¦Û¤v¼g¼g ¸Õ¸Õ¬Ý.

¥t¥~, ­þÃ䦳VBA©Ò¦³¨ç¼Æ, ¥H¤Î»¡©ú©O?
VBA «ü¥O»¡©ú ±q¤¤ªº½d¨Ò ¥h¤@¤@Åé·|

TOP

ÁÂÁ¤j¤j,
©Ò¥H XlFormulas ¬OFind.´M§ä«ürange¸Ì­±ªº­È¥]§t¤½¸Õ
¤]´N¬O»¡ ¦pªGrange¸Ìªº­È¬O¸g¥Ñ¹Bºâ±o¨ìªº ´N­n¥Îxlformulas??

¨º ¦pªG»¡ range ¸Ì­±ªº­È­è¦n¦³¼Æ­Ó¬Û¦Pªº, ¨ºfind ¥i¥HŪ¶Ü?¬OÀH«KŪ¨ú¨ä¤¤¤@­Ó?

¥t¥~, ­þÃ䦳VBA©Ò¦³¨ç¼Æ, ¥H¤Î»¡©ú©O?

TOP

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

¦^´_ 13# Helain
Find ·j´Mªº±ø¥ó¦pªG¨S­«·s³]©w ·|¨Ì·Ó¤W¦¸ªº·j´M±ø¥ó¨Ó´M§äªº
Set QrF = R_Profit.Find(a, , xlFormulas, xlWhole)
§A´M§äªº¬O¼Æ¦r »P ¤u§@ªí¤WÅã¥Ü¥Xªºªº¤å¦r(¼Æ¦r)¤£¤@¼Ë
  1. Sub test_3()
  2. Dim R_Profit As Range        'R's 2nd profit table
  3. Dim M_Profit As Range        'M's 2nd profit table
  4. Dim M_MaxProfit As Range     'For different Qr, M's Max profit
  5. Set R_Profit = Range(Cells(20, 2), Cells(30, 12))
  6. Set M_Profit = Range(Cells(2, 2), Cells(12, 12))
  7. Set M_MaxProfit = Range(Cells(15, 2), Cells(15, 12))
  8. MsgBox Application.Max(R_Profit)
  9. Set QrF = R_Profit.Find(Application.Max(R_Profit), , xlFormulas, xlWhole)
  10. MsgBox QrF.Text
  11.     If Not QrF Is Nothing Then
  12.         R = QrF.row
  13.         C = QrF.column
  14.     End If
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 12# GBKEE



Find ·j´Mªº±ø¥ó·|¨Ì·Ó¤W¦¸ªº·j´M±ø¥ó¨Ó´M§äªº (§Ú¤£À´³o¥y¸Üªº·N«ä)

Set QrF = R_Profit.Find(Application.Max(R_Profit), ,xlvalues , xlWhole)

QrFÁÙ¬OŪ¤£¨ìªF¦è

TOP

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

¦^´_ 11# Helain
Find ·j´Mªº±ø¥ó¦pªG¨S­«·s³]©w ·|¨Ì·Ó¤W¦¸ªº·j´M±ø¥ó¨Ó´M§äªº
Set QrF = R_Profit.Find(Application.Max(R_Profit), , , xlWhole)    ³o»yªk¨S«ü©w¬O (¤½¦¡ ¤º®e µù¸Ñ)  ¦³«ü©wÀx¦s®æªº¤º®e»Ý§¹¥þ¬Û¦P

½Ð¬d¬Ý7¼Ó Hsieh¶Wª©ªº¹Ï¤ù, ¬d¬d¬Ý  §A Excel¤W¦¸ªº·j´M±ø¥ó¬O (¤½¦¡ ¤º®e µù¸Ñ).

TOP

©êºp, §ÚQrFÁÙ¬OŪ¤£¨ì¸ê®Æ£®

Sub test_3()

Dim R_Profit As Range        'R's 2nd profit table
Dim M_Profit As Range        'M's 2nd profit table
Dim M_MaxProfit As Range     'For different Qr, M's Max profit

Set R_Profit = Range(Cells(20, 2), Cells(30, 12))
Set M_Profit = Range(Cells(2, 2), Cells(12, 12))
Set M_MaxProfit = Range(Cells(15, 2), Cells(15, 12))

Set QrF = R_Profit.Find(Application.Max(R_Profit), , , xlWhole)

    If Not QrF Is Nothing Then
   
        A = QrF.Row
        B = QrF.Column
        
    End If
   

End Sub

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 : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD