Running Total
 

Hi

I have a value in cell A1 (Currency) and I want to be able to enter
value in B1 and have that added to the value in A1. Simple but I wan
the new value in A1 to remain even when I delete the value in B1 an
for A1 to be updated every time I enter a value in B1

Please advise an easy way to do this.

Thanks

V

--
uplink60
-----------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940
View this thread: http://www.excelforum.com/showthread.php?threadid=39052

Running Total
 

right click on the sheet tab and select view code. Paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
If Not IsEmpty(Target) Then
If IsNumeric(Target) Then
Range("A1").Value = Range("A1").Value + _
Range("B1").Value
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy

"uplink600" wrote
in message ...

Hi

I have a value in cell A1 (Currency) and I want to be able to enter a
value in B1 and have that added to the value in A1. Simple but I want
the new value in A1 to remain even when I delete the value in B1 and
for A1 to be updated every time I enter a value in B1

Please advise an easy way to do this.

Thanks

VC


--
uplink600
------------------------------------------------------------------------
uplink600's Profile:
http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=390521

Running Total
 

VC,
Copy this code, right-click on the sheet tab, select view code, and paste in the window that
appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address < "$B$1" Then Exit Sub
Application.EnableEvents = False
Range("A1").Value = Range("A1").Value + Range("B1").Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"uplink600" wrote in message
...

Hi

I have a value in cell A1 (Currency) and I want to be able to enter a
value in B1 and have that added to the value in A1. Simple but I want
the new value in A1 to remain even when I delete the value in B1 and
for A1 to be updated every time I enter a value in B1

Please advise an easy way to do this.

Thanks

VC


--
uplink600
------------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=390521

Running Total
 

This would go in the code module for the worksheet (in VBA editor, Project
Explorer, double-click on the line that shows the worksheet you want this to
work on)

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("B1")) Is Nothing) Then _
Range("A1").Value = Range("A1").Value + Range("B1").Value

End Sub

But note a weakness in this way of doing things: if you make a mistake in
the value you enter in B1 there is no going back - no record of what values
make up your running total. It is easy to make errors this way that are
untraceable.
--
- K Dales


"uplink600" wrote:


Hi

I have a value in cell A1 (Currency) and I want to be able to enter a
value in B1 and have that added to the value in A1. Simple but I want
the new value in A1 to remain even when I delete the value in B1 and
for A1 to be updated every time I enter a value in B1

Please advise an easy way to do this.

Thanks

VC


--
uplink600
------------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=390521

Running Total
 

Thanks Guys that works fine

Can you advise some code to use if I want to use a loop to work throug
rows 1 to 20 to do the same task.

Thank

--
uplink60
-----------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940
View this thread: http://www.excelforum.com/showthread.php?threadid=39052

Running Total
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
Target(1, 0).Value = Target(1, 0).Value + Target.Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"uplink600" wrote in message
...

Thanks Guys that works fine

Can you advise some code to use if I want to use a loop to work through
rows 1 to 20 to do the same task.

Thanks


--
uplink600
------------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=390521

Running Total
 

Just to add:
If a non numeric value is inadvertently entered in column B, this can throw
an error and leave you with events disabled - wondering what happened. It
is useful to insert a check to make sure the value to be added is numeric:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
if isnumeric(target.value) then
Target(1, 0).Value = Target(1, 0).Value + Target.Value
end if
ErrHandler:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B20")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
Target(1, 0).Value = Target(1, 0).Value + Target.Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"uplink600" wrote
in message
...

Thanks Guys that works fine

Can you advise some code to use if I want to use a loop to work through
rows 1 to 20 to do the same task.

Thanks


--
uplink600
------------------------------------------------------------------------
uplink600's Profile:
http://www.excelforum.com/member.php...fo&userid=9408
View this thread:
http://www.excelforum.com/showthread...hreadid=390521

Running Total
 

Message for Bernie

Thanks for that

Just one more thing that I forgot. I need this to work on variou
ranges such as B1:B20, B24:B34, B38:B48.

Can the code be ammended to work on more than one range of cells.

Thank

--
uplink60
-----------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...nfo&userid=940
View this thread: http://www.excelforum.com/showthread.php?threadid=39052

Running Total
 

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B20, B24:B34, B38:B48")) Is Nothing Then Exit Sub
If Target.Cells.Count 1 Then Exit Sub
Application.EnableEvents = False
Target(1, 0).Value = Target(1, 0).Value + Target.Value
Application.EnableEvents = True
End Sub

HTH,
Bernie
MS Excel MVP


"uplink600" wrote in message
...

Message for Bernie

Thanks for that

Just one more thing that I forgot. I need this to work on various
ranges such as B1:B20, B24:B34, B38:B48.

Can the code be ammended to work on more than one range of cells.

Thanks


--
uplink600
------------------------------------------------------------------------
uplink600's Profile: http://www.excelforum.com/member.php...fo&userid=9408
View this thread: http://www.excelforum.com/showthread...hreadid=390521