VBA Vlookup to print labels

Question

I've been running in circles for the past few days trying to figure out the best way to use vlookup using VBA.

I have this code so far:

Private Sub CommandButton21_Click() 'Print Pack

Dim ws As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim nbanho As Single, tgrids As Long, banho As Double, rng1 As Range, rng2 As Range

Set ws = Sheets("Grid Bath")
Set ws2 = Sheets("Orders")
Set ws3 = Sheets("CL Labels")
Set rng1 = ws.Range("A1:G1048576")
Set rng2 = ws2.Range("C1:I1048576")

bath = TextBox21.Value
    
nbath = Application.WorksheetFunction.VLookup(bath, rng1, 7, False)
tgrids = Application.WorksheetFunction.VLookup(bath, rng2, 7, False)

    ws3.Range("A1") = TextBox22.Value 'puid
    ws3.Range("A2") = Date 'Date
    ws3.Range("A3") = "Banho " & nbanho 'Bath Number
    ws3.Range("A4") = "Total Grids: " & tgrids
    ws3.Range("A5") = "*" & TextBox21.Value & "*" 'ID Bath
    ws3.Range("A6") = TextBox21.Value 'ID Carbonation
    
    'ws2.Range("A1:A6").PrintOut

End Sub

What I'm trying to do is, print a label using info from 2 different worksheets. However, I keep getting error

'1004' Unable to get the VLookup property of the WorksheetFunction class

I've looked everywhere in the internet, but none of the samples and answers I found, seemed to fix my issue.

Pᴇʜ Pᴇʜ · Accepted Answer · 2018-10-11T09:23:00

If the VLOOKUP() fails (can't find a match), you will get this error.

So you need to catch this error for example like …

nbath = ""
On Error Resume Next
nbath = Application.WorksheetFunction.VLookup(bath, rng1, 7, False)
On Error Goto 0

If nbath = "" Then
    Msgbox "The value '" & bath & "' could not be found in " & rng1.Resize(ColumnSize:=1).Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True)
    Exit Sub
End If

Additionally I recommend to use Option Explicit and declare all your variables properly.

VBA Vlookup to print labels

1 Answers