+ Reply to Thread
Results 1 to 4 of 4

Worksheetfunction.lookup works sometimes and errors out other times..

  1. #1
    Registered User
    Join Date
    08-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    39

    Worksheetfunction.lookup works sometimes and errors out other times..

    Hello,

    Much appreciation for your time and knowledge about VBA/excel.

    The following macro is intended to complete a data set; it uses the Lookup function to see if a particular value (primary key for a record) is found in the data set, and if not then it will add it else move on to next record.

    It looks through a list of primary keys and the lookup function works on most of the records, but then errors out about 2/3 the way through the list.

    Attached is the file and here's the particular section of the code in module 5 (a private sub initiated from code in module 4):
    Please Login or Register  to view this content.
    October PPA List_Template_Example.xlsm

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Worksheetfunction.lookup works sometimes and errors out other times..

    Where does this variable get set: LastRowBeforeGJLI

    Why not just use COUNTIF with the entire column as the search range? That way, you cannot omit any entries and, I believe, COUNTIFS is optimised to cope with blank entries.

    COUNTIFS() = 0, not found; COUNTIFS() > 0, found.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    08-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Worksheetfunction.lookup works sometimes and errors out other times..

    Excellent solution TMS. Appreciate the knowledge!

    Adam

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,090

    Re: Worksheetfunction.lookup works sometimes and errors out other times..

    You're welcome. Thanks for the rep.

+ 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. [SOLVED] Re-naming a sheet, works but with errors
    By Barttm888 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2015, 09:02 AM
  2. [SOLVED] Userform Login Errors - Works with One, but not the Other! *** CODING ERROR ***
    By JJFletcher in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-13-2014, 05:26 PM
  3. [SOLVED] VBA Program works some times and some times gives me a Run-time error '1004'?
    By redsab in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-13-2014, 11:15 PM
  4. [SOLVED] Dynamic US Map was working fine, but now I'm getting WorksheetFunction.Match 1004 errors
    By cdutton in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-23-2014, 10:42 AM
  5. Data validation - works with TAB or ENTER, errors from a Mouse Click
    By Buzz83 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-15-2013, 08:38 AM
  6. WorksheetFunction.lookup
    By thestappa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2010, 02:54 PM
  7. [SOLVED] Works when stepped-through, but errors when macro ran
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-21-2006, 09:50 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