Emoncada
03-18-2013, 11:21 AM
I am having issues with Vlookup in VBA, I think I might need to use something else possibly INDEX. I have the following.
Sub VbLookUp()
Dim item As Variant
Dim Desc As Variant
Dim UPC As Long
Dim ws As Worksheet
Dim wd As Worksheet
Set ws = ActiveWorkbook.Sheets("Capture")
Set wd = ActiveWorkbook.Sheets("Catalog")
'-------------------------------
'----Vlookup of Item
'-------------------------------
UPC = ws.OLEObjects("TxtUPC").Object.Value
item = Application.VLookup(UPC, wd.Range("B2:D800"), 2, False)
If IsError(item) Then
ws.OLEObjects("TxtItem").Object.Value = "Not Found"
Else:
ws.OLEObjects("TxtItem").Object.Value = item
End If
'-------------------------------
'----Vlookup Description of Item
'-------------------------------
Desc = Application.VLookup(UPC, wd.Range("B2:D800"), 3, False)
If IsError(item) Then
ws.OLEObjects("TxtDesc").Object.Value = "Not Found"
ws.Shapes("CmdAdd").Visible = True
Else:
ws.OLEObjects("TxtDesc").Object.Value = Desc
End If
End Sub
I have
wd.Range(B:B) as Number Format
wd.Range(C:C) as Text Format
wd.Range(D:D) as Text Format
I can get it to work but noticed it doesn't find some.
I think it has something to do with the value having a "0" in the middle.
Any Ideas?
Sub VbLookUp()
Dim item As Variant
Dim Desc As Variant
Dim UPC As Long
Dim ws As Worksheet
Dim wd As Worksheet
Set ws = ActiveWorkbook.Sheets("Capture")
Set wd = ActiveWorkbook.Sheets("Catalog")
'-------------------------------
'----Vlookup of Item
'-------------------------------
UPC = ws.OLEObjects("TxtUPC").Object.Value
item = Application.VLookup(UPC, wd.Range("B2:D800"), 2, False)
If IsError(item) Then
ws.OLEObjects("TxtItem").Object.Value = "Not Found"
Else:
ws.OLEObjects("TxtItem").Object.Value = item
End If
'-------------------------------
'----Vlookup Description of Item
'-------------------------------
Desc = Application.VLookup(UPC, wd.Range("B2:D800"), 3, False)
If IsError(item) Then
ws.OLEObjects("TxtDesc").Object.Value = "Not Found"
ws.Shapes("CmdAdd").Visible = True
Else:
ws.OLEObjects("TxtDesc").Object.Value = Desc
End If
End Sub
I have
wd.Range(B:B) as Number Format
wd.Range(C:C) as Text Format
wd.Range(D:D) as Text Format
I can get it to work but noticed it doesn't find some.
I think it has something to do with the value having a "0" in the middle.
Any Ideas?