VBA: changing a formula when columns are inserted / deleted
I know I should be able to work this out, but I'm tired after a day of dancing and cycling !
Anyway, on to the facts.
I have a complex VBA routine which uses named ranges to ensure that if columns are inserted / deleted on the target Worksheet everything works properly.
However I can't work out how to make what follows equally invariant to column insertion / deletion:
The relevant line of code reads ActiveCell.Formula = "=N1"
Then I copy this formula down the whole column.
For example if a column is inserted to the left of column N I'd like this piece of VBA to read ActiveCell.Formula = "=O1"
Or if a column is deleted to the left of column N I'd like this piece of VBA to read ActiveCell.Formula = "=M1"
I was wondering if perhaps I could name N1 itself and extract the address of this named range (single cell) and use that to define the formula that is written - but I am unsure of the syntax to do that.
Or is there a better way ?