Need to Adjust Macro Formula

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
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:

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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Mar 14, 2002
Messages
1,181
Use the formulaarray in ONLY cell M2, then write code to fill it down
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
This should be the formula for M3:

Code:
=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

Board Regular
Joined
Jul 20, 2007
Messages
60

ADVERTISEMENT

This should be the formula for M3:

Code:
=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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Those formulas are exactly the same.:eek:

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

Oops!:oops:

Just saw my mistake.:)

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

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,793
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jul 20, 2007
Messages
60
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:

Code:
=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:

Code:
=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

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Alexander

I must be losing my eyesight and or sanity.:eek:
 

floridagunner

Board Regular
Joined
Jul 20, 2007
Messages
60
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:

Code:
=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:

Code:
=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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,804
Messages
5,598,158
Members
414,214
Latest member
marketingnumbersguy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top