VBA Excel Line throwing Error Code 13 Type Mismatch

Question

I'm trying to do a VBA version of an excel function which works. In Excel, here is my function.

=INDEX(L:L,MATCH(U2&V2,I:I&D:D,0))

To replicate this, I created this in VBA. Code is below, but I've tested all my variables first.

poiUnitCost is a range that returns L:L correctly
poiItemNum is a range that returns I:I correctly
poiPONum is a range that returns D:D correctly
Item returns U2 value equivalent
PONum returns V2 value equivalent

I'm using Val function because some numbers may be formatted as text or numbers, so I want it to match either way.

POPrice = Application.WorksheetFunction.Index(poiUnitCost, Application.WorksheetFunction.Match(Val(Item) & Val(PONum), poiItemNum & poiPONum, 0))

I'm getting error code 13 type mismatch when I run it.

Answer 1

The problem is poiItemNum & poiPONum. You can't concatenate two multi-cell ranges.

The normal solution here is to use Worksheet.Evaluate with the formula as text:

POPrice = Worksheets("YourSheetName").Evaluate("=INDEX(L:L,MATCH(U2&V2,I:I&D:D,0))")

If you want to use variables instead of U2&V2, then add outer quotes:

POPrice = Worksheets("YourSheetName").Evaluate("=INDEX(L:L,MATCH(""" & Item & PONum & """,I:I&D:D,0))")