Copying array formula to last row using VBA.

[Folshot]

Hi all,

I am using the following macro to copy an array formula from cell B2 down to the last row:

Sub Copy_Array_Formula_Down_To_Last_Row()

Application.ScreenUpdating = False

Dim LastRow As Long

    With Sheets("SN Validity Check")
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Range("B2:B" & LastRow).FormulaArray = _
        "=INDEX('Master MICL'!AQ:AQ,MATCH($A2&""ACTIVE"",'Master MICL'!H:H&'Master MICL'!N:N,0))"
    End With

Application.ScreenUpdating = True

End Sub

The formula is copied to the last row of column B, however the formula always refers to value $A2, i.e.
- Cell B3 should contain formula:

{=INDEX('Master MICL'!AQ:AQ,MATCH($A3&"ACTIVE",'Master MICL'!H:H&'Master MICL'!N:N,0))}

- Cell B4 should contain formula:

{=INDEX('Master MICL'!AQ:AQ,MATCH($A4&"ACTIVE",'Master MICL'!H:H&'Master MICL'!N:N,0))}

But they all contain

{=INDEX('Master MICL'!AQ:AQ,MATCH($A2"&"ACTIVE",'Master MICL'!H:H&'Master MICL'!N:N,0))}

Any ideas anyone?

Thanks.