+ Reply to Thread
Results 1 to 5 of 5

Lookup trying to return a value out of range

  1. #1
    Registered User
    Join Date
    06-18-2009
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    8

    Lookup trying to return a value out of range

    The following code is part of a macro which I use to check a list of entries against a list of known staff. If the name being checked in the code does not appear in the list, the user is notified and the name is added to the list. The lookup function always returns a value from the column it references, so the code checks to see whether the returned value is equal to the name it is checking for. However, the first entry on the staff list begins with "Ash", and when the code is looking for a value beginning with "Adb", the lookup function will try to return a name before the entry beginning with "Ash". Since there is no value before "Ash", it returns an error. As a temporary solution, I have added someone named "Aardvark" to the beginning of the staff list, but I would prefer to have a more elegant soltion.

    Thanks.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Lookup trying to return a value out of range

    What value do you want returned for a non-existant name?
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  3. #3
    Registered User
    Join Date
    06-18-2009
    Location
    America
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Lookup trying to return a value out of range

    I would love it if it just returned a null value. That's easy to work with.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Lookup trying to return a value out of range

    Perhaps simply switch to a simple Match, run @ Application level you can set the result to be a Variant to trap errors without need for double evaluation

    Please Login or Register  to view this content.
    Obviously you can use the IsNumeric test to be the precursor for subsequently adding the name to the list or whatever it is you want to do.

  5. #5
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Lookup trying to return a value out of range

    Or you could use

    Please Login or Register  to view this content.
    The CStr converts the error values to strings so there is no type mismatch error.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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