GBKEE

¦^´_ 8# am0251
Sub test()
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim ItemName As String
z = 2
Do While Range("A" & z).Value <> ""
'ItemNam = Worksheets("Sheets1").Range("A" & z).Value       '§AªºªþÀɤ¤¨S¦³Sheets1¤u§@ªí
'x = Evaluate("MATCH(""ItemNam"",Sheet(Sheets2)!A:A,0)")    §AªºªþÀɤ¤¨S¦³Sheets2¤u§@ªí
'x = Application.WorksheetFunction.Match(Worksheets("Sheets1").Range("A" & z).Value, Sheets("Sheets2").Range("A:A")) '¦p¤Wªº¿ù»~
ItemNam = Worksheets("Sheet1").Range("A" & z).Value
x = Evaluate("MATCH(""" & ItemNam & """,Sheet2!A:A,0)")   'ItemNam¬OÅܼƭn¥Î&¨Ó³s±µ
x = Application.WorksheetFunction.Match(Worksheets("Sheet1").Range("A" & z).Value, Sheets("Sheet2").Range("A:A"))
Debug.Print x, y
z = z + 1
Loop
End Sub

GBKEE

¦^´_ 28# Happkkevin

1. Public Function VLMatch(tt$, KK$, NN$, DALL As Range, dt As Range) As String
   
2.     'Match  §ä¨ì­È«h¶Ç¦^¼Æ¦r,¨S§ä¨ì­È«h¶Ç¦^¿ù»~­È
   
3.     'VLookup   §ä¨ì¶Ç¦^¦r¦ê,¨S§ä¨ì¶Ç¦^¿ù»~­È
   
4.     Dim i As Variant, j As Variant  'Åܼƻݳ]©w¬°Variant¸ê®Æ«¬ºA
   
5.     'Variant¸ê®Æ«¬ºA¬O©Ò¦³¨S³Q©ú½T«Å§i¬°¥ô¤@¨ä¥L«¬ºA
   
6.     i = Application.Match(KK, dt, 0)  'vba¤¤¨Ï¥Î¤u§@ªí¨ç¼Æ->  Application.¤u§@ªí¨ç¼Æ
   
7.     '½Ð¬Ývba¤¤¥i¨Ï¥Îªº¤u§@ªí¨ç¼Æ
   
8.     j = Application.VLookup(tt, DALL, i, 0)
   
9.     '***   Evaluate("VLookup(tt, DALL, i, 0)")=[VLookup(tt, DALL, i, 0)]
   
10.     '¤£±µ¨üÅÜ¼Æ tt, DALL, i   ***
    
11.     If IsError(j) Then   'j¶Ç¦^¿ù»~­È
    
12.         VLMatch = NN
    
13.     Else:    'j¶Ç¦^¦r¦ê
    
14.         If j = "" Then VLMatch = NN Else VLMatch = j
    
15.     End If
    
16. End Function

½Æ»s¥N½X