Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doh!!
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 ------------------- help me pleas -- oberon.blac ----------------------------------------------------------------------- oberon.black's Profile: http://www.excelforum.com/member.php...fo&userid=2673 View this thread: http://www.excelforum.com/showthread.php?threadid=40186 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Doh!!
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 "oberon.black" wrote: the following code works great with only one issue, the formula I have setup will calculate income for all columns between 'c12' and 'd12' and it calculates expenses for all columns between 'e12' and 'f12'. So when I insert a new column between 'c12' and 'd12' everything is fine for the income calculation but the expense calculation gets screwed because the spreadsheet creates the new column. I also cannot add the expense 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 -------------------- help me please -- oberon.black ------------------------------------------------------------------------ oberon.black's Profile: http://www.excelforum.com/member.php...o&userid=26732 View this thread: http://www.excelforum.com/showthread...hreadid=401866 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|