Consulting

Results 1 to 4 of 4

Thread: Solved: VLookup Code Error in form - Runtime Error 1004

  1. #1
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location

    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 !
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [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]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Jul 2009
    Posts
    157
    Location
    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 !

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •