Macro for copying and modifying formula

grimr33f3r

New Member
Joined
Jul 19, 2007
Messages
5
Hi all, hope you can help with this one.
Im using this formula
=INDEX(range,SMALL(IF(siccodes=G$2,ROW(siccodes)),ROW(1:1)),3) but have to use it several times in different locations, rather than copy and paste and then editing it I was trying to record a macro to make the adjustments for me as I paste it.

This is what Ive pretty much got:
Selection.FormulaArray = _
"=INDEX(range,SMALL(IF(siccodes=RC[-2],ROW(siccodes)),ROW(R[-679])),3)"
End Sub

verythings fine except for the part in bold, I need it to stay at 1:1, however everything I have tried has screwed it up.
Any help is appreciated, thanks
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

grimr33f3r

New Member
Joined
Jul 19, 2007
Messages
5
Thanks, but Ive already tried that, it just does the row number +1. I need it to input 1:1 no matter where I place the formula.
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
What if you make it a two step process? First store the formula to a defined name. Then use that defined name to feed into the array formula? Like before, I haven't tested this...
 
Upvote 0

grimr33f3r

New Member
Joined
Jul 19, 2007
Messages
5
Just tried it, but unfortunatly it doesnt like it, gonna keep playing about with it and see if i can get it to work, thanks again
 
Upvote 0

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,368
I didn't explain, the reason for the named range route was to avoid the RC reference style. So, hopefully you tried with normal A1 references...
 
Upvote 0

grimr33f3r

New Member
Joined
Jul 19, 2007
Messages
5
Yeah I gathered that much, not sure if you meant to try putting the whole formula in a defined range, it didnt like that. However I did put just the 1:1 into a defined cell, that seems to work now. Thanks :)
 
Upvote 0

grimr33f3r

New Member
Joined
Jul 19, 2007
Messages
5
Ahh just hit a snag, now the row(1:1) doesnt increase when I need to auto fill I need it to increase per row to 2:2 3:3 etc...
Ill keep trying but any inputs appreciated, thanks
 
Upvote 0

Forum statistics

Threads
1,190,703
Messages
5,982,390
Members
439,777
Latest member
daleEH

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