Syntax error found in vba vlookup
 

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

Syntax error found in vba vlookup
 

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

Syntax error found in vba vlookup
 

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? =)

"Bob Phillips" wrote:

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

Syntax error found in vba vlookup
 

Not exactly sure what you are trying to do, but this compiles and runs

Sub Look_For_Part()
Dim Look_UP_Value As Variant
Dim oWb As Workbook
Dim oData1 As Range
Dim oData2 As Range
Dim currentRow As Long
Dim NumOfRows As Long
Dim answer1$, answer

Set oWb = Workbooks.Open _
(Filename:="G:\Asia\Product\Operations\2" & _
"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 Cells(Rows.Count, "C").End(xlUp).Row
answer1$ = Cells(currentRow, "D")
Look_UP_Value = Trim(Range("c" & currentRow).Value) 'Select One Part Number
answer = Application.Match(Look_UP_Value, oData1, 0)
If Not IsError(answer) Then
answer = Application.Match(Look_UP_Value, oData2, 0)
If Not IsError(answer) Then
answer1$ = Trim(answer)
If (Len(answer1$)) 1 Then
Cells(currentRow, "h") = "1"
Else
Cells(currentRow, "h") = "2"
End If
End If
End If
Next currentRow

End Sub


--
HTH

Bob Phillips

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

"Bob Phillips" wrote:

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

Syntax error found in vba vlookup
 

Hi Bob,

Thanks for your help. If let's say i wish to count the number of lines(in
the data array) that contains the same word variable(answer$), how should i
do it?

"Bob Phillips" wrote:

Not exactly sure what you are trying to do, but this compiles and runs

Sub Look_For_Part()
Dim Look_UP_Value As Variant
Dim oWb As Workbook
Dim oData1 As Range
Dim oData2 As Range
Dim currentRow As Long
Dim NumOfRows As Long
Dim answer1$, answer

Set oWb = Workbooks.Open _
(Filename:="G:\Asia\Product\Operations\2" & _
"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 Cells(Rows.Count, "C").End(xlUp).Row
answer1$ = Cells(currentRow, "D")
Look_UP_Value = Trim(Range("c" & currentRow).Value) 'Select One Part Number
answer = Application.Match(Look_UP_Value, oData1, 0)
If Not IsError(answer) Then
answer = Application.Match(Look_UP_Value, oData2, 0)
If Not IsError(answer) Then
answer1$ = Trim(answer)
If (Len(answer1$)) 1 Then
Cells(currentRow, "h") = "1"
Else
Cells(currentRow, "h") = "2"
End If
End If
End If
Next currentRow

End Sub


--
HTH

Bob Phillips

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

"Bob Phillips" wrote:

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