CODEWITHSUNDEEP
vbaexcelexcel-formula
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Index Match in VBA Formula property

I have been trying to loop through a range of cells and apply an index match . So, far, the index match is working only for the first row of the range (so its not looping). I am providing the code.

Dim LastRow As Long
Sheets("REPORT").Select
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("C2:C" & LastRow).Formula "=INDEX('2609'!C:C,MATCH('REPORT'!A2,'2609'!E:E,FALSE))"

Upvotes: 1

Views: 827

Answers (1)

Mrig
Mrig

Reputation: 11712

Write

Range("C2:C" & LastRow).Formula "=INDEX('2609'!C:C,MATCH('REPORT'!A2,'2609'!E:E,FALSE))"

as 

Range("C2:C" & LastRow).Formula ="=INDEX('2609'!C:C,MATCH('REPORT'!A2,'2609'!E:E,FALSE))"

you are missing = sign.

Your code can be written as

Sub Demo()
    Dim ws As Worksheet
    Dim LastRow As Long

    Set ws = ThisWorkbook.Sheets("REPORT")
    With ws
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        .Range("C2:C" & LastRow).Formula = "=INDEX('2609'!C:C,MATCH('REPORT'!A2,'2609'!E:E,FALSE))"
    End With
End Sub

Upvotes: 1

Related Questions