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

 

[xenou]

What formula would you like to have in, for instance, Cell M3? Looking at this formula it appears to me it would be...exactly the same in all cells.

 

[BobUmlas]

Use the formulaarray in ONLY cell M2, then write code to fill it down

 

[floridagunner]

This should be the formula for M3:

=INDEX('FA SPLIT MASTER'!$A$1:$J$57670,SMALL(IF('FA SPLIT MASTER'!$A$1:$J$57670=$A3,ROW('FA SPLIT MASTER'!$A$1:$J$57670)),MOD(COUNTIF($A$2:$A3,$A3)-1,COUNTIF('FA SPLIT MASTER'!$A$1:$A$57670,$A3))+1),6)

 

[floridagunner]

This should be the formula for M3:

=INDEX('FA SPLIT MASTER'!$A$1:$J$57670,SMALL(IF('FA SPLIT MASTER'!$A$1:$J$57670=$A3,ROW('FA SPLIT MASTER'!$A$1:$J$57670)),MOD(COUNTIF($A$2:$A3,$A3)-1,COUNTIF('FA SPLIT MASTER'!$A$1:$A$57670,$A3))+1),6)

 

[Norie]

Those formulas are exactly the same.

Also every reference in the formula is absolute, so I can't see Excel or VBA altering them.

Oops!

Just saw my mistake.

But I think the 2nd point about the absolute references still stands.

 

[xenou]

Try Bob Umlas' solution...looks like it will work.

Note: I see there is a relative Row reference ($A2 changes to $A3). I suppose you could also use R1C1 formula style as another solution...

Regards.

 

[floridagunner]

No the formulas are not the same they have changed. In my first posting the cell references were $A2. This was for cell M2.

If you notice for Cell M3 They have changed to $A3

Example for cell M2:

=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)"

Example for cell M3:

=INDEX('FA SPLIT MASTER'!$A$1:$J$57670,SMALL(IF('FA SPLIT MASTER'!$A$1:$J$57670=$A3,ROW('FA SPLIT MASTER'!$A$1:$J$57670)),MOD(COUNTIF($A$2:$A3,$A3)-1,COUNTIF('FA SPLIT MASTER'!$A$1:$A$57670,$A3))+1),6)

 

[Norie]

Alexander

I must be losing my eyesight and or sanity.

 

[floridagunner]

No the formulas are not the same they have changed. In my first posting the cell references were $A2. This was for cell M2.

If you notice for Cell M3 They have changed to $A3

Example for cell M2:

=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)"

Example for cell M3:

=INDEX('FA SPLIT MASTER'!$A$1:$J$57670,SMALL(IF('FA SPLIT MASTER'!$A$1:$J$57670=$A3,ROW('FA SPLIT MASTER'!$A$1:$J$57670)),MOD(COUNTIF($A$2:$A3,$A3)-1,COUNTIF('FA SPLIT MASTER'!$A$1:$A$57670,$A3))+1),6)