Runtime Error 5, invalid procedure or call argument

Question

I am trying to assign Cell E8 in Sheet"Report" with an Index Match formula with a dynamic range. The range is from Sheet"Data"

I have found the last row (LR) and last column (lc). The run time error occurs at the last line: Cell("E8").formula = "=...."

This is the code:

Sub report()
    Dim LR As Long, lc As Long, first As Long, proxy As String

    Sheets("Data").Select

    'Finding the first filled cell by moving down from A1
    first = Sheets("Data").Range("A1").End(xlDown).Row

    'The first row has column headers: Name, ID number, etc... SO I assign it to the next row where the first data entry is
    first = first + 1

    LR = Sheets("Data").Range("A" & first).End(xlDown).Row
    lc = Sheets("Data").Range("A" & first).End(xlToRight).Column

    Sheets("Report").Select
    proxy = "=IFERROR(INDEX(Data!$A$10:" & Cells(LR, lc).Address & ",MATCH(Report!$C$3,Data!$A$10:" & Cells(LR, 1).Address & ",0),MATCH(Report!$C8,Data!A$9:" & Cells(9, lc).Address & ",0)),'N/A')"

    Cells("E8").Formula = proxy
End Sub

Shouldn't it be `Data!$A$" & first & ":" & ...` instead of `Data!$A$10:" & ...`? — , Mar 22 '16 at 05:13

score 1 · Answer 1 · answered Mar 22 '16 at 05:26

Sub report()
    Dim LR As Long, lc As Long, first As Long, proxy As String

    With Sheets("Data")
        'Finding the first filled cell by moving down from A1
        first = .Range("A1").End(xlDown).Row

        'The first row has column headers: Name, ID number, etc... SO I assign it to the next row where the first data entry is
        first = first + 1

        LR = .Range("A" & Rows.Count).End(xlUp).Row
        lc = .Range("A" & first - 1).End(xlToRight).Column
    End With

    proxy = "=IFERROR(INDEX(Data!$A$10:" & Cells(LR, lc).Address & ",MATCH(Report!$C$3,Data!$A$10:" & Cells(LR, 1).Address & ",0),MATCH(Report!$C8,Data!A$9:" & Cells(9, lc).Address & ",0)),""N/A"")"
    Sheets("Report").Range("E8").Formula = proxy
End Sub

score 0 · Answer 2 · edited May 23 '17 at 12:07

You are using single quotes to wrap 'N/A'. You need double quotes and because they are quotes within a quoted string, you need to double them up. Additionally, the Range.Cells property does not accept the same style of cell address references that a Range object does.

proxy = "=IFERROR(INDEX(Data!$A$10:" & Cells(LR, lc).Address & _
                ",MATCH(Report!$C$3,Data!$A$10:" & Cells(LR, 1).Address & _
                ",0),MATCH(Report!$C8,Data!A$9:" & Cells(9, lc).Address & _
                ",0)),""N/A"")"
Sheets("Report").Select
Range("E8").Formula = proxy

Here is a quick rewrite that gets away from using Worksheet.Select¹ method and the implicit ActiveSheet property.

Sub report()
    Dim lr As Long, lc As Long, first As Long, proxy As String

    With Worksheets("Data")

        'Finding the first filled cell by moving down from A1
        first = .Range("A1").End(xlDown).Row

        'The first row has column headers: Name, ID number, etc... SO I assign it to the next row where the first data entry is
        first = first + 1

        lr = .Range("A" & first).End(xlDown).Row
        lc = .Range("A" & first).End(xlToRight).Column

    End With

    proxy = "=IFERROR(INDEX(Data!$A$10:" & Cells(lr, lc).Address & _
                    ",MATCH(Report!$C$3,Data!$A$10:" & Cells(lr, 1).Address & _
                    ",0),MATCH(Report!$C8,Data!A$9:" & Cells(9, lc).Address & _
                    ",0)),""NA"")"

    With Worksheets("Report")
        .Range("E8").Formula = proxy
        'alternate with .Cells as one of these
        '.Cells(8, "E").Formula = proxy
        '.Cells(8, 5).Formula = proxy
    End With
End Sub

.¹ _{See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.}

Cells("E8") should be replaced with Range since it seems to be Oversight error. — Sixthsense, Mar 22 '16 at 05:27
Thanks for catching that Sixth. I was planning to get back to the code for a quick rewrite and only mentioned the Cells/Range in my narrative, missing changing it in the code. — , Mar 22 '16 at 05:32

Runtime Error 5, invalid procedure or call argument

2 Answers2