CODEWITHSUNDEEP
excelvba
jgodoyle2
jgodoyle2

Reputation: 1

Using INDEX MATCH in VBA with Variable Lookup Locations

I am having trouble using variables within the lookup criteria in Index Match. Some background: I use the following code to set a variable's value to the row # of whatever cell contains "Current" within column B:

Dim rowHeaderNum As Integer

    rowHeaderNum = 0

    On Error Resume Next
    rowHeaderNum = Application.Match("Current", ActiveSheet.Range("B:B"), 0)
    On Error GoTo 0

Then I use the below to store the column # of the cell within the row 'rowHeaderNum' that contains the value "CurrentActual" to another variable:

Dim currActColNum As Integer

currActColNum = 0
    currActColNum = Application.Match("CurrentActual", Rows(rowHeaderNum & ":" & rowHeaderNum), 0)

Below is the Index Match line that I can't get to work:

Dim currActRev As Double

    currActRev = Application.Index(Columns(currActColNum), Application.Match("Gross Operating Profit", Columns("N:N"), 0))

currActRev will store a dollar amount. The Match function will always use column N as the lookup array. When I run the Index Match line I get a

type mismatch

error in the debugger.

Upvotes: 0

Views: 1142

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57673

Using WorksheetFunction …

Application.Match and On Error Resume Next does not work, because Application.Match does not throw exceptions you need to use WorksheetFunction.Match instead.

According to the documentation the WorksheetFunction.Match method it returns a Double so you need to Dim RowHeaderNum As Double.

Dim RowHeaderNum As Double
'RowHeaderNum = 0 'not needed it is always 0 after dim

On Error Resume Next
RowHeaderNum = Application.WorksheetFunction.Match("Current", ActiveSheet.Range("B:B"), False)
On Error GoTo 0

Furthermore you need to check if RowHeaderNum is 0 and stop proceeding otherwise the following code will fail because row 0 does not exist.

If RowHeaderNum = 0 Then
    MsgBox "'Current' not found."
    Exit Sub
End If

You need to do exactly the same here

Dim CurrActColNum As Double
On Error Resume Next
CurrActColNum = Application.WorksheetFunction.Match("CurrentActual", Rows(RowHeaderNum), False)
On Error GoTo 0

If CurrActColNum = 0 Then
    MsgBox "'CurrentActual' not found."
    Exit Sub
End If

Finally the WorksheetFunction.Index method returns a Variant not a Double and you need error handling here too.

Dim currActRev As Variant
On Error Resume Next
currActRev = Application.WorksheetFunction.Index(Columns(CurrActColNum), Application.WorksheetFunction.Match("Gross Operating Profit", Columns("N:N"), False))
On Error GoTo 0

Debug.Print currActRev 'print result in immediate window

Using Application …

Note that you can also use the Application.Match and Application.Index (without WorksheetFunction) but then you cannot use On Error … and you have to check for errors using IsError(). Also your variables need to be declared as Variant then because Application.Match can either return a typo Double or a type Error.

Dim RowHeaderNum As Variant
RowHeaderNum = Application.Match("Current", ActiveSheet.Range("B:B"), False)

If IsError(RowHeaderNum) Then
    MsgBox "'Current' not found."
    Exit Sub
End If

Dim CurrActColNum As Variant
CurrActColNum = Application.Match("CurrentActual", Rows(RowHeaderNum), False)

If IsError(CurrActColNum) Then
    MsgBox "'CurrentActual' not found."
    Exit Sub
End If


Dim currActRev As Variant, currMatch As Variant
currMatch = Application.Match("Gross Operating Profit", Columns("N:N"), False)
If Not IsError(currMatch) Then
    currActRev = Application.Index(Columns(CurrActColNum), currMatch)
End If

Debug.Print currActRev 'print result in immediate window

Upvotes: 1

Related Questions