+ Reply to Thread
Results 1 to 7 of 7

Code for VBA VLookup result blank if column index number is blank

  1. 03-03-2014, 06:13 PM #1
    Sc0tt1e
    Sc0tt1e is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Code for VBA VLookup result blank if column index number is blank

    The below piece of code carries out a vlookup on a defined cells value and produces a result in sheet one, however if the column index number in sheet 2 (Database) is empty the result 00/01/1900 is produced.

    I'm not sure how to say leave the result blank if the column index number is blank, can anyone help out with this additional bit of code please?

    Excel 2010
    Userform = Tab 1
    Database = Tab 2

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-03-2014, 06:31 PM #2
    TMS
    TMS is offline
    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,446

    Re: Code for VBA VLookup result blank if column index number is blank

    Try:

    Please Login or Register  to view this content.

    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

    Reply With QuoteRegister To Reply
  3. 03-03-2014, 06:33 PM #3
    TMS
    TMS is offline
    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,446

    Re: Code for VBA VLookup result blank if column index number is blank

    Actually, that fixes an error condition ... you might need to replicate the VLOOKUP and test the result for blank.

    Regards, TMS
    Reply With QuoteRegister To Reply
  4. 03-03-2014, 06:41 PM #4
    Sc0tt1e
    Sc0tt1e is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Code for VBA VLookup result blank if column index number is blank

    What about ISBLANK instead of IFERROR?
    Reply With QuoteRegister To Reply
  5. 03-03-2014, 08:05 PM #5
    TMS
    TMS is offline
    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,446

    Re: Code for VBA VLookup result blank if column index number is blank

    You have to do the VLOOKUP to return the value. So, you don't know if it's blank until you get it, and then you can test it.

    Essentially:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    says, if the VLOOKUP returns null/blank, make the cell blank, otherwise return the VLOOKUP value.

    And you need:

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    to cater for the search value not being found.


    Regards, TMS
    Reply With QuoteRegister To Reply
  6. 03-06-2014, 05:26 PM #6
    Sc0tt1e
    Sc0tt1e is offline
    Forum Contributor
    Join Date
    04-12-2013
    Location
    Usually at work, in the UK
    MS-Off Ver
    Excel 2010
    Posts
    639

    Re: Code for VBA VLookup result blank if column index number is blank

    I've tried to apply the theory but it isn't quite working, any suggestions what I am doing wrong?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-06-2014, 05:55 PM #7
    TMS
    TMS is offline
    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,446

    Re: Code for VBA VLookup result blank if column index number is blank

    Double up all the quotes in the formula ... except those at the start and end of the formula.

    Regards, TMS
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How can I make VLOOKUP return nothing at all if column index is blank?
    By gibson2503 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-19-2013, 09:25 AM
  2. [SOLVED] If A colum blank, result in B column if B blank result in C colum how do i make formula?
    By vengatvj in forum Excel General
    Replies: 2
    Last Post: 10-18-2013, 11:18 AM
  3. [SOLVED] code so that shape is not visible when result of formula is blank
    By ea223 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-24-2013, 05:02 PM
  4. Code to add result in related spreadsheet but ONLY if cell is blank.
    By Tanyab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-12-2013, 06:06 AM
  5. VLookup to give Blank result
    By mrcois in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2010, 11:50 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1