+ Reply to Thread
Results 1 to 7 of 7

WorksheetFunction.Lookup with Vlookup and Row

  1. #1
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    WorksheetFunction.Lookup with Vlookup and Row

    Hello Excel VBA Experts,

    I have a formula that I use in Excel and it's working.

    The formula below basically returns me EITHER the ROW INDEX of approximate match (via vlookup) OR #N/A (which means there's no approx. match)

    Please Login or Register  to view this content.
    My question is, when I tried to mimic the same formula above to VBA, it's giving me a "Run-time error '13' Type Mismatch".

    Below is part of the VBA code.

    Please Login or Register  to view this content.
    May I know how do I get it to work like the excel function formula?

    Thanks

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,241

    Re: WorksheetFunction.Lookup with Vlookup and Row

    If you post a workbook, you don't get untested answers
    Something *like*:
    Please Login or Register  to view this content.
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: WorksheetFunction.Lookup with Vlookup and Row

    Hi protonLeah,

    I think you misunderstood my question.

    The Excel formula gives the result that I want.

    What I'm trying to accomplish here is that, I'd like to accomplish the way the formula has done but in VBA.

    So basically, in VBA, I have to change each value that's in B8 where the formula is at ...VLOOKUP(B8....) to be in VBA.

    I've tried to mimic it, that's why I have this
    Please Login or Register  to view this content.
    I tried to put EVALUATE in front of that in VBA, and still gave me an error value, instead of the Excel Formula result

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,520

    Re: WorksheetFunction.Lookup with Vlookup and Row

    Some formulas can't be converted to VBA using Application.WorksheetFunction and when they can't you should use Evaluate as protonLeah has shown.

    The problem with this formula is that you are trying to compare a range of values to a single value, that's not possible, directly, in VBA.
    If posting code please use code tags, see here.

  5. #5
    Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    Excel 2013
    Posts
    893

    Re: WorksheetFunction.Lookup with Vlookup and Row

    I see...

    Thanks Norie for the input.

    That means I'll have to use .Find instead.

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,520

    Re: WorksheetFunction.Lookup with Vlookup and Row

    Can't you use the suggested Evaluate?

    That should give the same result as the formula on the sheet, all you'll need to do is replace the hard-coded references.

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,241

    Re: WorksheetFunction.Lookup with Vlookup and Row

    Maybe you can modify this:
    Please Login or Register  to view this content.
    Last edited by protonLeah; 11-25-2020 at 07:05 PM.

+ 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. vba WorksheetFunction Lookup(2,1/Countif(........))
    By Xceller in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-03-2020, 10:29 PM
  2. [SOLVED] Vlookup Error - unable to get the vlookup property of the worksheetfunction class
    By forrestgump1980 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2019, 02:59 PM
  3. [SOLVED] WorksheetFunction.Lookup Error
    By mohan.r1980 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-22-2017, 03:07 AM
  4. [SOLVED] Worksheetfunction.lookup works sometimes and errors out other times..
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2015, 11:17 AM
  5. WorksheetFunction.lookup
    By thestappa in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-25-2010, 02:54 PM
  6. WorksheetFunction.VLookup
    By Cbrehm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2010, 11:37 AM
  7. vlookup worksheetfunction
    By ben in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-15-2005, 06:45 PM

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