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

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

Doh!!
 

Help you how? You are the one who wrote the code. If you add an
additional column you have to adjust references to columns to the right
of that new column. One way to make that adjustment is with the Offset
property of the Range object. For more look up XL VBA help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

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