VBA INDEX MATCH Run-time error '1004': Application-defined or object-defined error

Question

I am getting a run-time error: "application-defined or object-defined error when I run the following code

    For i = 4 To lastOutputCol
        If (Sheets("Mort Rates").Cells(1, i).Value Mod 5) = 0 Then
            For x = 2 To numOutputRows

                With Sheets("Mort Rates")
                .Cells(x, i) = Application.WorksheetFunction.Index(Sheets("Input").Range(Sheets("Input").Cells(17, dbPerkCol), _
                Sheets("Input").Cells(lastInputRow, dbPerkCol + 2)), Application.WorksheetFunction.Match(.Cells(x, 2) & .Cells(1, i) & .Cells(x, 3), _
                Sheets("Input").Range(Sheets("Input").Cells(17, 1), Sheets("Input").Cells(lastInputRow, 1)), 0), 1)
                End With

            Next x
        End If
    Next i

The issue is with line

.Cells(x, i) = Application.WorksheetFunction.Index(Sheets("Input").Range(Sheets("Input").Cells(17, dbPerkCol), _
Sheets("Input").Cells(lastInputRow, dbPerkCol + 2)), Application.WorksheetFunction.Match(.Cells(x, 2) & .Cells(1, i) & .Cells(x, 3), _
Sheets("Input").Range(Sheets("Input").Cells(17, 1), Sheets("Input").Cells(lastInputRow, 1)), 0), 1)

I've checked and all of the variables used are correct.

EDIT:

I am trying to INDEX MATCH from an input sheet tables and return the correct value. I am now using this code:

    For i = 4 To lastOutputCol
        If (Sheets("Mort Rates").Cells(1, i).Value Mod 5) = 0 Then
            For x = 2 To numOutputRows

                With Sheets("Mort Rates")
                .Cells(x, i).Value = Application.WorksheetFunction.Index(Sheets("Input").Range(Sheets("Input").Cells(17, dbPerkCol), _
                Sheets("Input").Cells(lastInputRow, dbPerkCol + 2)), Application.WorksheetFunction.Match(.Cells(x, 2) & .Cells(1, i) & .Cells(x, 3), _
                Sheets("Input").Range(Sheets("Input").Cells(17, 1), Sheets("Input").Cells(lastInputRow, 1)), 0), 1)
                End With

            Next x
        End If
    Next i

The code works for a while(fills about half the cells I want) and then I get this error: "Unable to get the Mtch property of the WorksheetFunction class"

What line? Also, when you use With xyz, you should add a period . before the range objects you wish to link. So (I guess) .Cells(x,i).Value = ... if you want the cell on "Mort Rates" sheet to get the value from your index formula. Also, Sheets("X").Range(Cells(1,1)) will likely throw an error. Somewhat annoyingly, you must still specify the sheet for Cells() even if you did so for the outer Range. So, Sheets("Input").Range(Sheets("Input").Cells(17, 1), Sheets("Input").Cells(lastInputRow, 1)) for one example. — BruceWayne
Are you sure the information is correct? For example, .Cells(x, 2) & .Cells(1, i) & .Cells(x, 3) will concatenate the values in those cells. If it's 1, 2, and 3 in the cells, respectively, that will become 123 in the formula. Also how did you declare the variables, such as dbPerkCol? — BruceWayne
@PaulR1999, in addition, what are you trying to achieve? I have corrected the obvious syntax errors in your code in the answer below but as BruceWayne mentioned, there might be some logical errors that we can't correct if we don't know what you are trying to achieve, and so I can't even guarantee the answer — AAA

AAA AAA · Accepted Answer · 2019-07-16T14:27:40

Dim ws as Worksheet
set ws = ThisWorkbook.Worksheets("Input")
For i = 4 To lastOutputCol
    If (Sheets("Mort Rates").Cells(1, i).Value Mod 5) = 0 Then
        For x = 2 To numOutputRows

            With ThisWorkbook.Sheets("Mort Rates")
                .Cells(x, i).Value = Application.Index(ws.Range(ws.Cells(17, dbPerkCol), _
                ws.Cells(lastInputRow, dbPerkCol + 2)), Application.Match(.Cells(x, 2) & .Cells(1, i) & .Cells(x, 3), _
                ws.Range(ws.Cells(17, 1), ws.Cells(lastInputRow, 1)), 0), 1)
            End With

        Next x
    End If
Next i

VBA INDEX MATCH Run-time error '1004': Application-defined or object-defined error

1 Answers