Bot tested, but the formula build looks incorrect. Try using directly like
this

Sub Look_For_Part()
Dim Look_UP_Value As Variant
Dim oWb As Workbook
Dim oData1 As Range
Dim oData2 As Range

Set oWb = Workbooks.Open _
(Filename:="G:\Asia\Product\Operations\ML-Part
Adjustments\testing1.xls")
Set oData1 = oWb.Worksheets("Sheet1").Range("A1:A65536")
Set oData2 = oWb.Worksheets("Sheet1").Range("C1:C15")
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 = Application.VLookup(Look_UP_Value, oData1, 1, False)
If Not IsError(answer) Then
answer = Application.VLookup(Look_UP_Value, oData2, 1, False)
If Not IsError(answer) Then
Answer1$ = Trim(answer)
If (Count(Answer1$)) 1 Then
Cells(currentRow, "h") = "1"
Else
Cells(currentRow, "h") = "2"
End If
End If
End I


--
HTH

Bob Phillips

"Junior728" wrote in message
...
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