[µo°Ý] VBA ¤¤¤Þ¥Î Mach ¨ç¼Æ°ÝÃD

hsu5120

½Ð°Ý¦U¦ì¤j¤j¡G
³o¤@¬qµ{¦¡¤¤¡AXX °Ñ¼Æ­n¦p¦ó§ï¼g¡A¤~¯à¥¿½T¶]¥X­È¡H

­ã´£³¡ªL

Range("N2") = "=MATCH(""" & arr_str_check(i) & """," & xx & ",0)"

c_c_lai

¦^´_ 1# hsu5120

¤U¸ü (35.54 KB)

2016-5-1 10:45

1.     i = 5
   
2.     ii = Range("U2").End(xlDown).Row
   
3.     xx = "U3:U19"
   
4. 
   
5.     s = WorksheetFunction.Match(arr_str_check(i), [A10:I10], 0)
   
6.     [A12] = Cells(10, s)
   
7.     s = WorksheetFunction.Match(arr_str_check(i), arr_str_check, 0) - 1
   
8.     MsgBox "arr_str_check(i) = " & arr_str_check(i) & vbCrLf & "arr_str_check(s) = " & arr_str_check(s)
   
9. End Sub

½Æ»s¥N½X

hsu5120

ÁÂÁ¦U¦ì¤j¤j¼ö¤ßªº¦^ÂСA¨ä¹ê·Q­n±N
1.³o¤@¦æ    xx="U3:U19"  ¡A§ï¦¨  xx="U3:U" & ii (ii ¬°ÅܼÆ)¡C
2.µM«á ±N ³o¤G¦æµ{¦¡¡G
    xx="U3:U" & ii
   Range("N4") = "=MATCH(""" & arr_str_check(i) & """, " & xx & ",0)"

3.¦X¨Ö¦¨ ¤@¦æµ{¦¡(¦p¤U)¡G
Range("N6") = "=MATCH(""" & arr_str_check(i) & """, ""U3:U""" & ii & """,0)"     '³o¤@¦æ¤@ª½¦³°ÝÃD

½Ð°Ý¤j¤j³o¤@¦æ¦p¦ó­×§ï¡A¥ýÁÂÁ¤j¤j ¼ö¤ßªº¦^ÂСC

c_c_lai

Range("N2") = "=MATCH(""" & arr_str_check(i) & """," & xx & ",0)"
­ã´£³¡ªL µoªí©ó 2016-5-1 10:20

³o¨à±a¤Jªº xx ¬O¦r¦ê«¬ºA¡A¨ç¦¡ªº MATCH() ³ºµM¯à±µ¨ü¡A
¤Ï¤§¦pªG±a¤J WorksheetFunction.Match() «K¤£¦æ¤F¡A

WorksheetFunction.Match(arr_str_check(i), xx, 0)

°õ¦æ¶¥¬q¿ù»~ '1004'
µLªk¨ú±oÃþ§O WorksheetFunction ªº Match ÄÝ©Ê
¦Ó¥²¶·«Å§i¦¨ Range/Object ¤~¥i¥H¡C

Set xx = [U3:U19]
WorksheetFunction.Match(arr_str_check(i), xx, 0)

c_c_lai

ÁÂÁ¦U¦ì¤j¤j¼ö¤ßªº¦^ÂСA¨ä¹ê·Q­n±N
1.³o¤@¦æ    xx="U3:U19"  ¡A§ï¦¨  xx="U3:U" & ii (ii ¬°ÅܼÆ)¡C
2 ...
hsu5120 µoªí©ó 2016-5-1 11:12

xx = "U3:U"
ii = 19
Range("A8") = "=MATCH(""" & arr_str_check(i) & """," & xx & ii & ",0)"
¤W¤U¨â¦æ§¡¬°¥¿½T»yªk¡C
Range("A8") = "=MATCH(""" & arr_str_check(i) & """,U3:U" & ii & ",0)"

­ã´£³¡ªL

¦^´_ 5# c_c_lai


WorksheetFunction.Match(arr_str_check(i), xx, 0)
§ï¥Î¡G
M = WorksheetFunction.Match(arr_str_check(i), Range(xx), 0)
M = Application.Match(arr_str_check(i), Range(xx), 0)

c_c_lai

¦^´_ 7# ­ã´£³¡ªL
Tks a lot!