VBA: How do I write an If statement using a true/false method and VLookup code?-CodePudding

I struggled with phrasing the question title, sorry. I created a VBA form. In the linebx combo bx I have the following code, that prefills the PilotBx and TailBx text boxes.

My issue is when the linebx is filled in with a value that is not in that linebx drop down (not in the VLookup range) it sends an error message

"Runtime error ‘1004’: Unable to get the VLookup property of the worksheetFunction class"

I know what this means, I'm just not sure how to get around it.

I need the code to prefill the PilotBx and TailBx text boxes, if the LineBx matches, the drop down (VLookup range) and if it doesn't I need it to accept the LineBxnumber entered and leave the PilotBx and TailBx text boxes blank.

Please let me know if you have any questions or details I can give to help. Thanks in advance for any help!

Here is a visual for the form I created:

    Dim z As Double
    Dim h As Double
    Dim k As String
    
        z = LineBx.Value
        h = Application.WorksheetFunction.VLookup(LineBx.Value, Sheets("StepBrief").Range("A2:E43"), 1, False)
    
    If h = z Then
        k = True
        
    Else
        k = False
        
    End If
    
    If k = True Then
        TailBx.Text = Application.WorksheetFunction.VLookup(z, Sheets("StepBrief").Range("A2:E43"), 3, False)
        PilotBx.Text = Application.WorksheetFunction.VLookup(z, Sheets("StepBrief").Range("A2:E43"), 2, False)
    
    Else
        TailBx.Value = ""
        PilotBx.Value = ""
        
    End If

CodePudding user response：

You can go with:

Dim h As Double
Dim k As String
    
z = LineBx.Value
h = -1
on error resume next
h = Application.WorksheetFunction.VLookup(LineBx.Value, Sheets("StepBrief").Range("A2:E43"), 1, False)
on error goto 0

It executes you lookup, does not raise an error when lookup not found. h will be -1 what will not be equal to z. You can use any value to set h to as long as it will not be equal to z you are good.

CodePudding user response：

I suppressed the error message by adding On Error Resume Next to the top and "Cleared" the PilotBx and TailBx I felt like that was a cheap way to get around it, but it did work.


    z = LineBx.Value
    
On Error Resume Next
    TailBx.Value = ""
    PilotBx.Value = ""

    TailBx.Text = Application.WorksheetFunction.VLookup(z, Sheets("StepBrief").Range("A2:E43"), 3, False)
    PilotBx.Text = Application.WorksheetFunction.VLookup(z, Sheets("StepBrief").Range("A2:E43"), 2, False)