Add a new cell value to the old one?

M

mowen

Hi

I want to add the new cell value to the old?

example
If the value is 10 in A3 and the sum realted cell A5=10. Then cell A
get a new value, 13, then the cell value in A5 should be 23 and so on.

It is possible to do that direct in excel or vba?

Run
 
B

Bob Phillips

Hi Rune,

With VBA you can

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A3")) Is Nothing Then
With Target
.Offset(2, 0).Value = .Offset(2, 0).Value + .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is worksheet event code, so it goes in the worksheet code module.
Right-click on the sheet tab, select View Code from the menu, and paste the
code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

mowen

Hi Bob Phillips

Tank you for the help. It work properly well for my little example.

I have another question for you, if I want to have the target cell i
another workbook or sheet how will the code be then?

best regards
Run
 
B

Bob Phillips

Hi Rune,

This is for a different worksheet

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A3")) Is Nothing Then
With Worksheets("Sheet3").Range("A3")
.Value = .Value + Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is for a difefrent workbook (but it must be open)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A3")) Is Nothing Then
With Workbooks("QDE Addin.xls").Worksheets("Sheet1").Range("A3")
.Value = .Value + Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top