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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
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
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
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
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
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,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

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
Back
Top