Excel Circular Reference SUM HELP!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Const WS_RANGE As String = "I:I"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
For Each c In Intersect(Target, Me.Range(WS_RANGE)).Cells
If Application.IsNumber(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End If
End Sub


Gord Dibben MS Excel MVP



On Fri, 5 Jun 2009 12:44:01 -0700, SpecOpBookie
wrote:

Thanks for the quick replies everyone. Daniel, do I need to edit this macro
to ONLY run for the "I" Column? I do not want to add 3500 to all numbers i
input. just to all of the numbers i input in the "I" Column THANKS!

"Daniel.C" wrote:

Hi.
Paste the following macro in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
If IsNumeric(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End Sub
(Right click on the sheet tab, select "View code" and paste the macro)
HTH
Daniel

Hi all, I'm fairly new with Excel so let's see if I can get a quick answer
for my question .

I am trying to add a base number to whatever number i Input within the same
cell. For example: The Cell I am working with is I4. The base number I want
is 3500. I want that added to whatever I type in the cell and summed in the
same cell. So if I type in 2200, the sum would be 5700. =SUM(3500,I4) does
not work. I get a 0 in the cell and if I try to input 2200, the formula just
erases. I need this formula for alot of cells all with the base number of
3500 and random input numbers. This IS possible isn't it?

Events may have become disabled. Unlike most other functions, the
EventsEnabled does not reset if a macro is halted.

Press Ctrl+G to pring up Immediate Action windown, and input
Application.EnableEvents = True
and press enter.

Now check to see if the macro works. Also, change the macro back to "Change"
not activate.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SpecOpBookie" wrote:

almost there haha... So now I cant get it to run. I changed the macro to
ACTIVATE and that didnt help, saved, exited, and re-opened it... notta. Excel
really isn't my strong suit.

"Luke M" wrote:

Macro limited to changes in column I

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address Like "$I$*" Then
Dim c As Range
For Each c In Target
If IsNumeric(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End If
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"SpecOpBookie" wrote:

Uhoh, not done yet. I checked, and the macro given to me changes ALL cells
that I input any numeric value into... I dont want that. can I specify a
range? just for the column "I"?

"Luke M" wrote:

Also a WARNING:
Deleting cells or cell contents creates a value of 0, thus activating macro.
This can lead to great annoyance if deleting entire columns/ranges of data.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Daniel.C" wrote:

Hi.
Paste the following macro in the sheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
For Each c In Target
If IsNumeric(c) Then
Application.EnableEvents = False
c = 3500 + c
Application.EnableEvents = True
End If
Next c
End Sub
(Right click on the sheet tab, select "View code" and paste the macro)
HTH
Daniel

Hi all, I'm fairly new with Excel so let's see if I can get a quick answer
for my question .

I am trying to add a base number to whatever number i Input within the same
cell. For example: The Cell I am working with is I4. The base number I want
is 3500. I want that added to whatever I type in the cell and summed in the
same cell. So if I type in 2200, the sum would be 5700. =SUM(3500,I4) does
not work. I get a 0 in the cell and if I try to input 2200, the formula just
erases. I need this formula for alot of cells all with the base number of
3500 and random input numbers. This IS possible isn't it?