Error in Index-Match function inside a loop VBA

Question

My index match loop had worked perfectly in a restricted sample, but when I expanded it to the whole sample it crashed. The problem is that at some point, it finds a value that cannot find in the rows range. It stops writing exactly in that cell. I tried to use isError, but it did not change anything and the code stops in the same cell.

Can you help me out please?!

Thanks!

Set inputs = Sheets("Inputs")
Set raw = Sheets("Raw")
Set interm = Sheets("Rel_Raw")
Set finaldata = Sheets("Data")



For i = cols + 1 To cols_2 Step 1
    For j = 2 To entries_r Step 1

        If Not IsError(finaldata.Cells(j, i).Value = Application.Index(interm.Range(interm.Cells(1, 1), interm.Cells(dim_r, dim_c)), IsError(Application.Match(finaldata.Cells(j, 1).Value, interm.Columns(1), 0)), IsError(Application.Match(finaldata.Cells(1, i).Value, interm.Rows(1), 0)))) Then
            finaldata.Cells(j, i).Value = Application.Index(interm.Range(interm.Cells(1, 1), interm.Cells(dim_r, dim_c)), Application.Match(finaldata.Cells(j, 1).Value, interm.Columns(1), 0), Application.Match(finaldata.Cells(1, i).Value, interm.Rows(1), 0))
        Else
            finaldata.Cells(j, i).Value = 0
        End If

    Next j
Next i

https://stackoverflow.com/questions/17751443/excel-vba-cant-get-a-match-error-unable-to-get-the-match-property-of-the-wor — BigBen, Apr 30 '20 at 17:37
Thanks for your quick response @BigBen. I changed WorksheetFunction. for Application. for the index and match functions, but it is not working. — Luis Antonio Gómez, Apr 30 '20 at 17:45
@BigBen I am not following. The code includes IsError() at the beginning of the If function. — Luis Antonio Gómez, Apr 30 '20 at 17:47
@BigBen, ok ok. I did it and still could not work. If Not IsError(finaldata.Cells(j, i).Value = Application.Index(interm.Range(interm.Cells(1, 1), interm.Cells(dim_r, dim_c)), Application.Match(finaldata.Cells(j, 1).Value, interm.Columns(1), 0), IsError(Application.Match(finaldata.Cells(1, i).Value, interm.Rows(1), 0)))) Then — Luis Antonio Gómez, Apr 30 '20 at 17:53
It would be beneficial to break this line up into steps - get the result of each `Application.Match` and test if each is an error. If both are not errors, *then* use them in `Index`. — BigBen, Apr 30 '20 at 17:56
@BigBen, I just checked by running an index-match formula in the spreadsheet and that cell throws NA. The problem is that it is not finding the column, the row is fine. Now, I added the IsError in the columns in VBA but still not working. If Not IsError(finaldata.Cells(j, i).Value = Application.Index(interm.Range(interm.Cells(1, 1), interm.Cells(dim_r, dim_c)), IsError(Application.Match(finaldata.Cells(j, 1).Value, interm.Columns(1), 0)), IsError(Application.Match(finaldata.Cells(1, i).Value, interm.Rows(1), 0)))) Then — Luis Antonio Gómez, Apr 30 '20 at 18:04
At this point, please [edit] your question with the revised code. — BigBen, Apr 30 '20 at 18:07

score 0 · Accepted Answer · answered Apr 30 '20 at 18:19

Your code could benefit from splitting out the Application.Match portions into their own lines. Store the result of the Application.Match in a Variant, then it might be easier to just use IsNumeric to test if they are a number, and if so proceed to use them inside Application.Index.

Dim rowMatch as Variant
rowMatch = Application.Match(finaldata.Cells(j, 1).Value, interm.Columns(1), 0)

Dim colMatch as Variant
colMatch = Application.Match(finaldata.Cells(1, i).Value, interm.Rows(1), 0)

Dim lookupRang as Range
Set lookupRange = interm.Range(interm.Cells(1, 1), interm.Cells(dim_r, dim_c))

If IsNumeric(rowMatch) And IsNumeric(colMatch) Then
    finaldata.Cells(j, i).Value = Application.Index(lookupRange, rowMatch, colMatch)
Else
    finaldata.Cells(j, i).Value  = 0
End If

Error in Index-Match function inside a loop VBA

1 Answers1