Excel, Trying Index, Match, to lookup values with VBA

Question

I am trying to look-up values at cell locations using VBA, I have searched google and all over stackoverflow before asking this because I can't seem to get it to work.

The following is the code I am trying to use, note that budgetcode references a cell containing one of the codes in the first column and mo references a cell that contains a number (1-12) or contains a short code (ytd, 1qtr, 2qtr, 3qtr). Example is that I want to pull CAD-NS for February (2), and I should get 5666.40.

Function eBudgetl(budgetcode As String, mo As String)
eBudgetl = Application.WorksheetFunction.Index(Range("Budget!G1:X5000"), _
   Application.WorksheetFunction.Match(budgetcode, Range("Budget!B1:B5000"), 0), _
   Application.WorksheetFunction.Match(mo, Range("Budget!G1:X1"), 0))
End Function

Here is part of the data I wish to lookup:

                                       1    2          3       4
CAD-NS      I   Net Sales           5264.0  5666.4  5614.9  5966.6
COSMAT      E   Material            6207.5  3660.0  3661.9  3560.9
COSDL       E   Direct Labor         610.4  105.3   167.1   123.6
CAD-MOIL    E   Indirect Labor       671.2  163.4   181.6   161.7
CAD-MOSAL   E   Salary Overhead      601.0  106.0   101.0   101.0

Here is the code in the cell that works, but I need to do in VBA. (The reason I need to do in vba is sometimes the budgetcode will contain 2+ references separated by a comma and I am going to use vba to separate them and look each up independently.)

=INDEX(Budget!$G$1:$X$5000,MATCH($F12,Budget!$B$1:$B$5000,0),MATCH(AN$1,Budget!$G$1:$X$1,0))

I appreciate any help very much, I have been at this for 3 days now.

Thanks,

Enoch

Answer 1

The issue is the Function parameter types.

When you call your Function with mo = cell AN1, containing the number 1, the Function type casts it to the String "1", which doen't exisit in the range Budget!$G$1:$X$1, since these are also numbers.

The solution is to use Variant as the function paramters type.

To make debugging this type of error easier, try not to do too much in a single line of code. Splitting the line into 2 x Match functions and an Index, would allow you to see the second match return an error.

Couple of other points:

• If your are calling this function as a UDF (ie called from a worksheet cell), it is better not to hard code the ranges. As written, the calls to eBudgetl would not automatically recalculate when any of their data changes.
• the Application object has a version of Index and Match so the WorksheetFunction calls are not required

Refactored version to demonstrate:

Function eBudgetl(rData As Range, rBudCode As Range, rMo As Range, budgetcode As Variant, mo As Variant)
    Dim rw As Variant
    Dim col As Variant

    With Application
        col = .Match(budgetcode, rBudCode, 0)
        rw = .Match(mo, rMo, 0)
        eBudgetl = .Index(rData, col, rw)
    End With
End Function

Called as

=eBudgetl(Budget!$G$1:$X$5000,Budget!$B$1:$B$5000,Budget!$G$1:$X$1,$F12,AN$1)