Doh!!

[oberon.black]

the following code works great with only one issue, the formula I hav
setup will calculate income for all columns between 'c12' and 'd12' an
it calculates expenses for all columns between 'e12' and 'f12'. So whe
I insert a new column between 'c12' and 'd12' everything is fine for th
income calculation but the expense calculation gets screwed because th
spreadsheet creates the new column. I also cannot add the expens
column to the proper location because 'f12' is now in the income area.

current code

Code
-------------------

Private Sub CommandButton1_Click()
If OptionButton1 = True Then
Range("d12").EntireColumn.Insert
Range("d11").Formula = TextBox1.Value
Else
Range("f12").EntireColumn.Insert
Range("f11").Formula = TextBox1.Value
End If
Unload Me
End Sub
Private Sub OptionButton1_Click()
Me.OptionButton1.Value = True
End Sub

 

[Guest]

Hi, Oberon,

How about adding Range Names to your worksheet, then referencing them in
your code instead of absolute cell addresses?

That way, if the cell to which the range name applies is moved by the
insertion or deletion of columns, your code will still refer to the correct
place on your worksheet.

e.g.

range("MyNamedRange").Formula = TextBox1.Value

Wherever the cell with the name MyNamedRange ends up, the code will always
point to it.

Hope this helps

pete
P