Solved: VBA VLookup Number return Text

[Emoncada]

I am having issues with Vlookup in VBA, I think I might need to use something else possibly INDEX. I have the following.

[VBA]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("B2800"), 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("B2800"), 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[/VBA]

I have
wd.Range(B:B) as Number Format
wd.Range(C:C) as Text Format
wd.Range(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?

[SamT]

Try this. If it works as you want, it is much faster than yours as written ATT.
[vba]Option Explicit

Sub VbLookUp()
Dim UPCItem As String 'item is a keyword. Never use keywords as names.
Dim Desc As String
Dim UPC As Long
Dim ws As Worksheet
Dim wd As Worksheet
Dim Found As Range
Set ws = ActiveWorkbook.Sheets("Capture")
Set wd = ActiveWorkbook.Sheets("Catalog")

''''Get UPC
UPC = CLng(ws.OLEObjects("TxtUPC").Object.Value)

'''Find Cell with UPC
Set Found = wd.Range("B2:B800").Find(UPC, LookIn:=xlValues)

''''Check needed values and set variable as indicated
If Found Is Nothing Then
UPCItem = "Not Found"
Desc = "Not Found"
Else
If Found.Offset(0, 1).Value <> "" Then _
UPCtem = CStr(Found.Offset(0, 1).Value)
If Found.Offset(0, 2).Value <> "" Then
Desc = CStr(Found.Offset(0, 2).Value)
Else
ws.Shapes("CmdAdd").Visible = True 'I'm not sure this belongs here, but,
're program flow, this is where it was.
End If
End If

''''Read Variables into OLEObjects
ws.OLEObjects("TxtItem").Object.Value = UPCItem
ws.OLEObjects("TxtDesc").Object.Value = Desc

'Recommend place it here
'ws.Shapes("CmdAdd").Visible = _
(Desc = "Not Found") _
Or _
(UPCItem = "Not Found")
'I set it true for both


End Sub

[/vba]

[Emoncada]

WOW SamT, that seems to work.

Thanks