Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 Posts

    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 ?

    Thanks
    Last edited by MartinM; 2014-07-26 at 15:58.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    If you name cell N1 "MyCell" then you can use the line:
    ActiveCell.Formula = "=" & Range("mycell").Address(False, False)

    Steve
    PS instead of using activecell and then copying you could select the range you want to copy to and use something like:
    Selection.Formula = "=" & Range("mycell").Address(False, False)

    or even give a range (or calc a range at runtime...):
    Range("A1:A100").Formula = "=" & Range("mycell").Address(False, False)
    Last edited by sdckapr; 2014-07-26 at 16:32.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 Posts
    Perfect !

    Thank you Steve, and for the alternative ideas which I'll think about.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Another alternative to the formula (depending on the real goal) is if you want it relative to the current selection. In this case you can use R1C1 nomenclature. For example if you want the cell to the immediate right of the active cell you can use:
    ActiveCell.FormulaR1C1 = "=RC[1]"

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jan 2004
    Location
    Wiltshire, UK
    Posts
    536
    Thanks
    1
    Thanked 46 Times in 44 Posts
    Thanks Steve, but it's the change in relative position which I am guarding against.

    Your first method does that for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •