Running total question
 

I am looking for a formula or function that will allow the display of a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would like
to be able to replace the value in C4 and have it added to what was shown in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would like
to be able to replace the value in C4 and have it added to what was shown in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

"Martin Fishlock" wrote in message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
Hello Martin,

Many thanks, I thought it might involve VBA. I had a similar one years ago
in Lotus123 which converetd the "total" cell from a formula to a value
before adding the new value and then changed it back, or something like it
anyway.
Trying yours now ;)

Again, many thanks.

Mike H

"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would
like
to be able to replace the value in C4 and have it added to what was shown
in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

"Martin Fishlock" wrote in message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Absolutely fantastic!!!!
I just wish I knew what you had done ;)

A thousand thanks!

Regards
Mike H
"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would
like
to be able to replace the value in C4 and have it added to what was shown
in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

OK, here goes:

this set up a flag to say that all variables need to be defined before use
it reduces mistakes.
Option Explicit


these are constants so it is easier to change rather than the code.
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
these are the replies for the error message it just saves time
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

this is the subroutine proper
Private Sub Worksheet_Change(ByVal Target As Range)

this checks if the changed cell given by target is the
cell here C4 (me.range(csCewllValue)
ie it checks that the changed cell and makes sure it is the one we want.
if you want to have a total in more than one cell then it is more difficult
as you need to then check the address and deal with the seperate one.
If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then

this check if c4 is not a number and therefore complain
I use an if then else to check multi conditions and if one condition is met then
it only does that one.
If Not IsNumeric(Me.Range(csCellValue)) Then
give a warning
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption

this check if e4 is not a number and therefore complain
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
give a warning
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
otherwise add them together
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub

Clear?

Hope it explains it a little more

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:


"Martin Fishlock" wrote in message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub


--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Absolutely fantastic!!!!
I just wish I knew what you had done ;)

A thousand thanks!

Regards
Mike H
"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would
like
to be able to replace the value in C4 and have it added to what was shown
in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

"Martin Fishlock" wrote in message
...
OK, here goes:

this set up a flag to say that all variables need to be defined before
use
it reduces mistakes.
Option Explicit


these are constants so it is easier to change rather than the code.
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
these are the replies for the error message it just saves time
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

this is the subroutine proper
Private Sub Worksheet_Change(ByVal Target As Range)

this checks if the changed cell given by target is the
cell here C4 (me.range(csCewllValue)
ie it checks that the changed cell and makes sure it is the one we
want.
if you want to have a total in more than one cell then it is more
difficult
as you need to then check the address and deal with the seperate one.
If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then

this check if c4 is not a number and therefore complain
I use an if then else to check multi conditions and if one condition
is met then
it only does that one.
If Not IsNumeric(Me.Range(csCellValue)) Then
give a warning
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption

this check if e4 is not a number and therefore complain
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
give a warning
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
otherwise add them together
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub

Clear?

Hope it explains it a little more

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:


"Martin Fishlock" wrote in message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te
screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub
Martin,

Many thanks for taking the time to explain the macro, I have printed it out
for further reading.
Just one more question re the macro, I tried to modify it to take in a
range, e.g, C4:C10 and E4:10, to see if it would work. I also changed all
references of"Cell" to "Range" It didn't. Any suggestions?

Regards

Mike H
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Absolutely fantastic!!!!
I just wish I knew what you had done ;)

A thousand thanks!

Regards
Mike H
"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of
a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would
like
to be able to replace the value in C4 and have it added to what was
shown
in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

It gets a little more complicated to check a range and I converted it little:


Option Explicit

Const crColValue As Long = 3
Const crColAns As Long = 5
Const crRowStart As Long = 4
Const crRowEnd As Long = 10
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rCell As Range
Dim rColValue As Long
Dim rColAns As Long
Dim rRow As Long

For Each rCell In Target
If Not (Intersect(rCell, _
Me.Range(Cells(crRowStart, crColValue), _
Cells(crRowEnd, crColValue))) _
Is Nothing) Then
rRow = rCell.Row
If Not IsNumeric(Me.Cells(rRow, crColValue)) Then
MsgBox csWarningStart & Me.Cells(rRow, crColValue).Address & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Cells(rRow, crColAns)) Then
MsgBox "Value in cell " & Me.Cells(rRow, crColAns).Address & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Cells(rRow, crColAns) = _
Me.Cells(rRow, crColAns) + Me.Cells(rRow, crColValue)
End If
End If
Next rCell
End Sub




--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:


"Martin Fishlock" wrote in message
...
OK, here goes:

this set up a flag to say that all variables need to be defined before
use
it reduces mistakes.
Option Explicit


these are constants so it is easier to change rather than the code.
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
these are the replies for the error message it just saves time
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

this is the subroutine proper
Private Sub Worksheet_Change(ByVal Target As Range)

this checks if the changed cell given by target is the
cell here C4 (me.range(csCewllValue)
ie it checks that the changed cell and makes sure it is the one we
want.
if you want to have a total in more than one cell then it is more
difficult
as you need to then check the address and deal with the seperate one.
If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then

this check if c4 is not a number and therefore complain
I use an if then else to check multi conditions and if one condition
is met then
it only does that one.
If Not IsNumeric(Me.Range(csCellValue)) Then
give a warning
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption

this check if e4 is not a number and therefore complain
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
give a warning
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
otherwise add them together
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub

Clear?

Hope it explains it a little more

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:


"Martin Fishlock" wrote in message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te
screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub
Martin,

Many thanks for taking the time to explain the macro, I have printed it out
for further reading.
Just one more question re the macro, I tried to modify it to take in a
range, e.g, C4:C10 and E4:10, to see if it would work. I also changed all
references of"Cell" to "Range" It didn't. Any suggestions?

Regards

Mike H
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Absolutely fantastic!!!!
I just wish I knew what you had done ;)

A thousand thanks!

Regards
Mike H
"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display of
a
running total.
If say, I have a value in C4 and this value is repeated in E4, I would
like
to be able to replace the value in C4 and have it added to what was
shown
in
E4.
So the value of E4 would continually increase by the value of what was
inputted into C4.
TIA
Mike Hyndman

Running total question
 

"Martin Fishlock" wrote in message
...
It gets a little more complicated to check a range and I converted it
little:

Option Explicit

Const crColValue As Long = 3
Const crColAns As Long = 5
Const crRowStart As Long = 4
Const crRowEnd As Long = 10
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rCell As Range
Dim rColValue As Long
Dim rColAns As Long
Dim rRow As Long

For Each rCell In Target
If Not (Intersect(rCell, _
Me.Range(Cells(crRowStart, crColValue), _
Cells(crRowEnd, crColValue))) _
Is Nothing) Then
rRow = rCell.Row
If Not IsNumeric(Me.Cells(rRow, crColValue)) Then
MsgBox csWarningStart & Me.Cells(rRow, crColValue).Address & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Cells(rRow, crColAns)) Then
MsgBox "Value in cell " & Me.Cells(rRow, crColAns).Address & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Cells(rRow, crColAns) = _
Me.Cells(rRow, crColAns) + Me.Cells(rRow, crColValue)
End If
End If
Next rCell
End Sub




--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Once again, a fantastic solution.
With your explanation of the first macro and the differences between it and
this one, I have learned a lot.
A million thanks ;)

Mike H
"Martin Fishlock" wrote in message
...
OK, here goes:

this set up a flag to say that all variables need to be defined
before
use
it reduces mistakes.
Option Explicit


these are constants so it is easier to change rather than the
code.
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
these are the replies for the error message it just saves time
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

this is the subroutine proper
Private Sub Worksheet_Change(ByVal Target As Range)

this checks if the changed cell given by target is the
cell here C4 (me.range(csCewllValue)
ie it checks that the changed cell and makes sure it is the one we
want.
if you want to have a total in more than one cell then it is more
difficult
as you need to then check the address and deal with the seperate
one.
If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then

this check if c4 is not a number and therefore complain
I use an if then else to check multi conditions and if one
condition
is met then
it only does that one.
If Not IsNumeric(Me.Range(csCellValue)) Then
give a warning
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption

this check if e4 is not a number and therefore complain
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
give a warning
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
otherwise add them together
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub

Clear?

Hope it explains it a little more

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Mike Hyndman" wrote:


"Martin Fishlock" wrote in
message
...
Hi Mike:

You need to use a marco (VBA) and it needs to be placed in the code
for
the
worksheet where you want the total to be kept.

To place it these right click on the sheet tab at the bottom of te
screen
and select 'View Code' then paste te following code in there.

It also checks for non-numerical values.

Option Explicit
Const csCellValue As String = "C4"
Const csCellAns As String = "E4"
Const csWarningStart As String = "The value in cell "
Const csWarningEnd As String = " should be a number!"
Const csWarningCaption As String = "Error..."

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Me.Range(csCellValue)) Is Nothing) Then
If Not IsNumeric(Me.Range(csCellValue)) Then
MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption
ElseIf Not IsNumeric(Me.Range(csCellAns)) Then
MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption
Else
Me.Range(csCellAns) = _
Me.Range(csCellValue) + Me.Range(csCellAns)
End If
End If
End Sub
Martin,

Many thanks for taking the time to explain the macro, I have printed it
out
for further reading.
Just one more question re the macro, I tried to modify it to take in a
range, e.g, C4:C10 and E4:10, to see if it would work. I also changed all
references of"Cell" to "Range" It didn't. Any suggestions?

Regards

Mike H
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Martin,

Absolutely fantastic!!!!
I just wish I knew what you had done ;)

A thousand thanks!

Regards
Mike H
"Mike Hyndman" wrote:

I am looking for a formula or function that will allow the display
of
a
running total.
If say, I have a value in C4 and this value is repeated in E4, I
would
like
to be able to replace the value in C4 and have it added to what was
shown
in
E4.
So the value of E4 would continually increase by the value of what
was
inputted into C4.
TIA
Mike Hyndman