Syntax error found in vba vlookup

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

hi thanks for ur help, however, when the macro go to "count" sentence, it
does not recognise it and produce a compile error-function not defined. What
is wrong? =)

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

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