MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Adding to a cell's previous value (Cell1+Cell2=Cell2New)


Posted by Richard Maule on August 08, 2000 8:26 AM

I have two cells. One is a running total (Cell1) and the other is a value over a specified period of time (Cell2). When I place a number in Cell1, I want Cell2 to add the value in Cell1 to itself creating a new value (Cell1+Cell2=Cell2New).

Unfortunately this creates a circle function. Making a 3rd cell would be simple but not effective. Is there any way to create a function or macro that will take the original value of a cell, add to it and then place the new value in the same cell?

Thanks!

Posted by Richard Maule on August 09, 0100 7:14 AM

This works but performs the calculation every single time I click on any cell on the page. I need to set this up as a macro that only runs when I unfocus Cell1 and preferably only after changing Cell1's value

Posted by Richard Maule on August 09, 0100 7:26 AM

Please follow up!

It worked! Thank you.

I have a follow-up question though. Say C11 was Cell1 and E11 was Cell2. I want this function to work for C11:C23 as Cell1s and E11:E23 as Cell2s. How would I do this? Putting in A1:A1i and A2:A2i doesn't work.

Posted by david on August 08, 0100 9:34 AM

inside the worksheet put
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Range("A2").Value <> 0 Then
Range("A1").Value = Range("A1").Value + Range("A2").Value
Range("A2").ClearContents
End If

End Sub

Posted by Tim Francis-Wright on August 08, 0100 10:12 AM

I don't think that the suggested change will work.
The following should work (A1 is Cell1 and A2 is Cell2):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not (Intersect(Target, Range("A1")) Is Nothing) Then
Range("A2").Value = Range("A1").Value + Range("A2").Value
End If
End Sub

Posted by David on August 08, 0100 8:59 PM

Why won't it work?