I have a macro that inserts a formula. Once the formula is inserted, I want to use autofill to "copy" the formula down. My problem is that the formula is not copying down correctly (it keeps the "n2" rather than "n3", "n4", etc.).

'code blows up

Formula1:="=INDIRECT(SUBSTITUTE(n2,"" "",""_""))"

'compile error

Formula1:="=INDIRECT(SUBSTITUTE("n2","" "",""_""))"

'works, but, when i copy and autofill formula down, the value stays as "n2"...when I want it to go to "n3"..."n4"..."n5"...etc.

Formula1:="=INDIRECT(SUBSTITUTE(""n2"","" "",""_""))"

Thanks for any help with the proper syntax.

T.J.