+ Reply to Thread
Results 1 to 22 of 22

Unable to Get userForm to Recognise code. With Error message regarding VLookup

  1. #1
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Anybody on the Forum who could help with this would be great. When I run the code in VBA (see below) I get the error message "Run TIme error '1004': Unable to get the VLookup property of the WorksheetFunction class". The Debug takes me to the first line after the 'With Me' command.

    Note when I run this from the UserForm a message box doed appear with the text I have input "This is an incorrect ID" as seen below in the code.


    Private Sub EntClientIDbx_AfterUpdate()
    'Check to see if value exists
    If WorksheetFunction.CountIf(Sheet1.Range("B:B"), Me.EntClientIDbx.Value) = 0 Then
    MsgBox "This is an incorrect ID"
    Me.EntClientIDbx.Value = ""
    Exit Sub
    End If
    'Lookup values based on first control
    With Me
    .ConfirmedClntName = Application.WorksheetFunction.VLookup(Me.EntClientIDbx, Sheet1.Range("Addressdata"), 3, 0) ------------Debug takes me here
    .ConfirmedClntAddr1 = Application.WorksheetFunction.VLookup(Me.EntClientIDbx, Sheet1.Range("Addressdata"), 4, 0)
    .ConfirmedClntCity = Application.WorksheetFunction.VLookup(Me.EntClientIDbx, Sheet1.Range("Addressdata"), 6, 0)
    .ConfirmedClntPcode = Application.WorksheetFunction.VLookup(Me.EntClientIDbx, Sheet1.Range("Addressdata"), 7, 0)
    End With
    End Sub

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Hi,

    The "Unable to get the VLookup property of the WorksheetFunction class" error means that your lookup value can't be found. If it's a number, you need to convert it, since the textbox contains text, not a true number. Perhaps this
    Please Login or Register  to view this content.
    As a side note, it would be more efficient to use Match to locate the position of the item rather than looking it up for every Vlookup call.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Deleted this useless part of the post....
    Another option is that Me.EntClientIDbx has a value that has no match.... that would return that error. (Textboxes are text, if the lookup is a number then you need to convert to a number)
    Last edited by Arkadi; 03-08-2017 at 09:03 AM.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  4. #4
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Thanks xlnitwit. I'll give that a try. The contents of the cell is text that I want VLookup to search not a number. It is a Client Lookup User Form i am trying to create.

  5. #5
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Thanks Arkadi, I did have a Sheet name anomaly, I'll check again.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Mark, I figured the error was related directly to the Vlookup, so I had removed that part of the comment, but perhaps that was too quick an assumption. Hopefully the sheet name issue is all that is wrong, if not we can follow up further.

  7. #7
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    I Recall I had to change the Sheet Name as it was called Sheet1 but had (Sheet2) in brackets.

  8. #8
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Hi xlnitwit, when I run this code I get a different error message "Run-time error '13': Type Mismatch" Debug takes me to the "lValue = Clng(me.EntClientIDBx.Value)" I thought it might be the "Clng" part as I think that refers to if there is a number in the cell I am looking at whereas I have text but after removing "Clng" I get the same message. Any thoughts??

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    well if it was Sheet1 outside the brackets (the sheet with the named range in it I mean), then the code ought to work the way it is... you could try Worksheets("Sheet2").Range..... That is if the one with a tab name of "Sheet2" is the one with your named range.

    Clng(me.EntClientIDBx.Value) converts the EntClientIDBx textbox to a Long data type... if it is not numerical you get the error. If you remove Clng, then because lvalue is dimmed as Long, it will still only accept numbers or give that error Type Mismatch.

  10. #10
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Yes, I had guessed, incorrectly, that you had an issue with looking up numbers. I would suggest something like this
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Thanks xlnitwit, No Error message this time....Result but my own message "This is an incorrect ID" pops up. I see that you have put in .Cells(theRow,3) etc but I was referring to the column. I've tried putting "theColumn in instead of "theRow" but that didn't work. Is there another code/text for Column I could insert here to direct it?
    Last edited by Mark Dynes; 03-08-2017 at 09:51 AM.

  12. #12
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    That makes sense.... I think! . I can see that the lvalue is dimmed as long (still not entirely sure what this means yet but I'll get there) does it have to be dimmed as long? can we change that Dim so that it will search text instead of numbers. I am trying to search for company names as opposed to client ID numbers.

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    I'm going to stay out of this for now, xlnitwit seems on top of it, and 2 people helping at the same time may confuse the issue....

    I'll keep an eye on the thread just in case, but xlnitwit is probably better than me, so I doubt you'll need me.

  14. #14
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Thanks Arkadi for your help, very much appreciated.

  15. #15
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    The Match line
    Please Login or Register  to view this content.
    looks for the value from EntClientIDbx in the first column of the range AddressData. If you're seeing the message, it's because that value is not present in that column (the same reason that your Vlookup code was failing). Without the workbook I couldn't tell you why it is not being found, but I suggest you check the spelling carefully and make sure there aren't leading or trailing spaces, for example, that are preventing an exact match.

  16. #16
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    umm sorry xlnitwit, not sure where I put this line of code?

  17. #17
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    That line is already in the last code I posted- I was explaining what it does.

  18. #18
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Aaaah OK, thanks.

  19. #19
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Hi xlnitwit, Legend, it works!!! Thank you very much. Onto the next bit now to get the information into another spreadsheet. Thanks again.

  20. #20
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Hi lxnitwit,

    So if I wanted to insert the data now in the userform would I set the Range simply as Range ("E8:E13")?

    I was trying to crib this from a youtube video but they just want to loop the information in another spreadsheet whereas I need this address information to appear on an Invoice. PS Do I need to start a new thread for this?

  21. #21
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Probably best to start a new question, I think.

  22. #22
    Registered User
    Join Date
    03-07-2017
    Location
    Stockport, England
    MS-Off Ver
    2007
    Posts
    91

    Re: Unable to Get userForm to Recognise code. With Error message regarding VLookup

    Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Error trying to import userform into Outlook: Unable to load UserForm.FRX
    By jeffclanders in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2016, 12:36 PM
  2. unable to resolve a #NUM! error message
    By Bill369 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-11-2015, 07:12 PM
  3. Excel 2010 - Unable to Insert Column - Error message not helping
    By tplenert in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-25-2014, 05:36 AM
  4. Replies: 3
    Last Post: 11-08-2012, 07:25 PM
  5. error message - unable to save external link values
    By BitsofColour in forum Excel General
    Replies: 0
    Last Post: 10-04-2005, 03:05 PM
  6. Error message: Unable to record
    By Tim in forum Excel General
    Replies: 0
    Last Post: 01-26-2005, 03:25 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1