Hi Sir,

Can someone help me to check if the Vlookup function below is correct or
not? the debugger always give me error. =) awaiting for your reply...my
intention is to lookup a value in a currentrow cell from another excel sheet
table array and return 1 or 2 to determine if there are more than one line
found for that particular value.(i am trying to detect duplicate values).


Sub Look_For_Part()

Dim Look_UP_Value As Variant

Workbooks.Open Filename:="G:\Asia\Product\Operations\ML-Part
Adjustments\testing1.xls"
Windows("testing1.xls").Activate


ProductGroup$ = "G:\Asia\Product\Operations\ML-Part Adjustments\testing1.xls"

Range("C2").Select
For currentRow = 2 To NumOfRows

Answer$ = Cells(currentRow, "D")

Look_UP_Value = Trim(Range("c" & currentRow)).Value 'Select One Part Number

Answer$ = "=IF(VLOOKUP(Look_Up_Value,'[" +
ProductGroup$"]SHEET1'!A1:A65536,1,FALSE),VLOOKUP(Look_Up_Value,'[" +
ProductGroup$ ]SHEET1'!C1:C15,15,FALSE),0"
Answer1$ = Trim(Answer$)

If (Count(Answer1$)) 1 Then
Cells(currentRow, "h") = "1"
Else
Cells(currentRow, "h") = "2"
End If

Next

End Sub