Macro for copying and modifying formula

[grimr33f3r]

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

 

[Fazza]

Welcome. What about R1 ? Fazza

 

[grimr33f3r]

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.

 

[Fazza]

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...

 

[grimr33f3r]

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

 

[Fazza]

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...

 

[grimr33f3r]

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

 

[grimr33f3r]

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