question

LukeHawtrey-7249 avatar image
0 Votes"
LukeHawtrey-7249 asked TvanStiphout answered

Trying to get Vlookup working in VBA

I have a worksheet that already uses Vlookup to find values. When I tried to use this code, it returned a "Error 1004" What could be the issue here?


If Me.txt_BarcodeScanIn.Value >= 0 Then

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("IN_OUT")

Dim lr As Long
lr = Application.WorksheetFunction.CountA(sh.Range("A:A"))

''''example vlookup Application.WorksheetFunction.VLookup(Me.cmb_Product, ThisWorkbook.Sheets("Product_Master").Range("B:D"), 2, 0)''''
Dim Barcode As String

Barcode = Me.txt_BarcodeScanIn.Value

sh.Range("A" & lr + 1).Value = lr
sh.Range("B" & lr + 1).Value = Application.WorksheetFunction.VLookup(Barcode, ThisWorkbook.Sheets("Master_Copy").Range("B:D"), 2, 0)
sh.Range("C" & lr + 1).Value = "Received"
sh.Range("D" & lr + 1).Value = 1
sh.Range("E" & lr + 1).Value = Application.WorksheetFunction.VLookup(Barcode, ThisWorkbook.Sheets("Master_Copy").Range("B:D"), 3, 0)
sh.Range("G" & lr + 1).Value = Me.txt_BarcodeScanIn.Value
sh.Range("H" & lr + 1).Value = Format(Now, "dd-mmm-yyyy")
Else: Me.txt_BarcodeScanIn.Value = ""

End If


''''''''''''Clear Boxes''''''''''''
Me.txt_BarcodeScanIn.Value = ""

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TvanStiphout avatar image
0 Votes"
TvanStiphout answered LukeHawtrey-7249 commented

Set a breakpoint at the top. Step through. At which line are you getting the error?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Currently the error is coming from the Vlookup function (For sh.range(B...). Although I have the data there, it always returns the 1004 error at the vlookup line.

0 Votes 0 ·
TvanStiphout avatar image
0 Votes"
TvanStiphout answered

At that point in the code, stepping through in the debugger, enter this in the Immediate window:
?Application.WorksheetFunction.VLookup(Barcode, ThisWorkbook.Sheets("Master_Copy").Range("B:D"), 2, 0)
This will evaluate the right-hand-side of the assignment, and may inform you better about what's not working. Maybe such worksheet does not exist.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.