CODEWITHSUNDEEP
excelvbamethodsrange
user3511424
user3511424

Reputation: 11

Method 'range' failed. VBA

Sub tester()

Dim total As Integer
Dim z As Integer

For z = 4 To 90

    If Not IsNull(Range("c" & z)) Then
        total = total + Application.WorksheetFunction.Index(Range("h2:h69171"), Application.WorksheetFunction.Match(Cells("c" & z) & Cells("A98").Value, Range("l2:l69171"), 1))
    End If

Next z

Range("b98").Value = total

End Sub

This code is giving me a "Method 'Range' of object'_Global' failed" (on the If... line). Can someone please help me to figure out why? Thanks in advance

Upvotes: 0

Views: 462

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33145

Sub tester()

    Dim total As Integer
    Dim z As Integer
    Dim wf As WorksheetFunction

    Set wf = Application.WorksheetFunction

    With Sheet1

        For z = 4 To 90
            If Not IsEmpty(.Cells(z, 3).Value) Then
                total = total + wf.Index(.Range("h2:h69171"), wf.Match(.Cells(z, 3).Value & .Cells(98, 1).Value, .Range("L2:L69171"), 1), 1)
            End If

        Next z

        .Range("b98").Value = total
    End With

End Sub

Cells takes a row and column argument. And it's good to prefix all your range references with a sheet - I use a With Block and start the range references with a period to accomplish that.

Also, when you don't supply a column argument to INDEX, it returns the whole row.

I created a wf variable simply to keep the code brief and readable.

Upvotes: 2

Related Questions