Solved: VLookup Code Error in form - Runtime Error 1004

I am trying to create a simple spreadsheet (see attached) with a form that the user would enter the Contributor Number and a Vlookup is performed on a range and when exiting the field would return the Contributor Name and Contributor Address into fields on the form. The code sets the range that is used in the Vlookup. When running this, I get an error: Runtime Error 1004: Unable to get the Vlookup property of the Worksheet Function class.

I have looked all over the web but cannot find a solution that works in this situation, so I am not sure where to go from here.

The code I am using is:

[VBA]
Private Sub ContributorNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim myrange
Set myrange = Worksheets("Contributors").Range("A1:B143")

ContributorName.Value = WorksheetFunction.VLookup(ContributorNumber.Value, myrange, 2, False)
If Err <> 0 Then
ContributorName.Value = "N/A"
End If

ContributorAddress.Value = WorksheetFunction.VLookup(ContributorNumber.Value, myrange, 3, False)

If Err <> 0 Then
ContributorAddress.Value = "N/A"
End If

End Sub

[/VBA]

Does anyone have an idea why this code is not working ?

When I tried to debug the code, when I hovered over ContributorNumber.Value, it showed the proper value entered into the form and I used values for ContributorNumber that I knew were present in the range I set-up.

Any advice would be greatly appreciated ! I think it is probably something small I am overlooking but cannot find it.

thanks !

[vba]

Private Sub ContributorNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim tmp As Variant
Dim myrange As Range
Set myrange = Worksheets("Contributors").Range("A1:C143")

On Error Resume Next
tmp = Application.VLookup(Val(ContributorNumber.Text), myrange, 2, False)
If Not IsEmpty(tmp) Then

ContributorName.Text = tmp
tmp = Empty

tmp = WorksheetFunction.VLookup(Val(ContributorNumber.Text), myrange, 3, False)
If Not IsEmpty(tmp) Then

ContributorAddress.Text = tmp
Else

ContributorAddress.Text = ""
End If
Else

ContributorName.Text = ""
End If

If Err <> 0 Then
Unit.Value = "N/A"
End If

End Sub
[/vba]

thanks as always, XLD.....I tried the code and it worked and then I went back and tried to understand the logic. I am still new to coding and am still learning.

Why is it necessary to put VAL before the ContributorNumber.Text and why must the .Text be added ?

I suppose the .Value code would not work for some reason in this example ?

I do appreciate the help !

The Val is to change the textbox input, which is always text, into numbers as your data is numeric. I prefer Text to Value, it seems more logical. I am not sure if I tried .Value, so it might work as well.