Excel Userform VBA VLOOKUP

Question

I am creating a userform where based on a drop down list of items called "ContractsList" I would like a Vlookup formula to return a text data in "TextBox 1".

I get an error message saying "Run-time error'1004': Unable to get the Vlookup property of the worksheetfunction class

Not sure what I am doing wrong, here is my code if anyone can spot the error.

Private Sub ContractsList_AfterUpdate()

If WorksheetFunction.CountIf(Sheet2.Range("A:A"),Me.ContractsList.Value) = 0 Then
MsgBox "This contract is not on the list"
Me.ContractsList.Value = ""
Exit Sub

End If
'Lookup values based on first control
With Me

.TextBox1 = Application.WorksheetFunction.VLookup(Me.TextBox1, ("B5:B72"), 2, 0)

End With
End Sub

I changed that to "A5:E72" still doesn't work. it looks like it doesn't like "Me.TextBox1, " — Abigal
I used ".TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Range("A5:E72"), 2, 0)" it worked once or twice and then stopped. — Abigal

Abigal Abigal · Accepted Answer · 2017-01-20T14:41:16

Finally got it to work as below:

Private Sub ContractsList_AfterUpdate()

If WorksheetFunction.CountIf(Sheet2.Range("A:A"), Me.ContractsList.Value) = 0 Then
MsgBox "This contract is not on the list"
Me.ContractsList.Value = ""
Exit Sub

End If
'Lookup values based on first control
With Me

.TextBox1 = Application.WorksheetFunction.VLookup(Me.ContractsList, Sheet2.Range("A5:E72"), 2, 0)

End With
End Sub

I just needed to add "Sheet2.Range("A5:E75")

Thank you all for your help.

Excel Userform VBA VLOOKUP

1 Answers