0
481
VistasVBA: How do I write an If statement using a true/false method and VLookup code?
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
2 Respuestas
Responde la pregunta0
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.
0
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)