bdsii
04-25-2011, 08:17 AM
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:
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
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 !
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:
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
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 !