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

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

I see...

Thanks Norie for the input.

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