CODEWITHSUNDEEP
vbaexcel
praggpt
praggpt

Reputation: 11

Runtime Error 5, invalid procedure or call argument

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

Upvotes: 1

Views: 393

Answers (2)

user4039065
user4039065

Reputation:

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.

Upvotes: 0

Sixthsense
Sixthsense

Reputation: 1975

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

Upvotes: 1

Related Questions