Need to Adjust Macro Formula

[floridagunner]

Hello I am trying to write a macro that will insert a formula directly into the cells in column M of my worksheet.

The worksheet has about 3400 rows. When I run the macro it does not adjust the cell reference in the formula so I am getting the same value in all 3400 rows.

I am using the following code:

Sub InsertFormula4()
With Sheets("FILE 1 C")
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("M2:M" & LastRow).FormulaArray = "=INDEX('FA SPLIT MASTER'!$A$1:$J$57670,SMALL(IF('FA SPLIT MASTER'!$A$1:$J$57670=$A2,ROW('FA SPLIT MASTER'!$A$1:$J$57670)),MOD(COUNTIF($A$2:$A2,$A2)-1,COUNTIF('FA SPLIT MASTER'!$A$1:$A$57670,$A2))+1),6)"
End With
Range("M2").Select
End Sub

The formula that I am using in the code above was written for Cell M2. And my problem is that it brings back exactly the same value in all the cells in Column M.

I hope somebody can point out what I am doing wrong.

Thanks
floridagunner

 

[floridagunner]

Do you happen to know the VBA code i need to fill it down?

Thanks

 

[xenou]

Just:
Range("M2").Copy Destination:=Range("M2:M3400")

Or:
Range("M2").Autofill Destination:=Range("M2:M3400")

Note: You can get code like this pretty easily with the macro recorder - its a great tool for getting syntax.

Regards