Running total question

[Mike Hyndman]

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

 

[Mike Hyndman]

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

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]

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,

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

A thousand thanks!

Regards
Mike H

 

[Mike Hyndman]

OK, here goes:

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

MsgBox csWarningStart & csCellValue & _
csWarningEnd, vbCritical + vbOKOnly, _
csWarningCaption

MsgBox "Value in cell " & csCellAns & _
" should be number", vbCritical + vbOKOnly, _
csWarningCaption

Clear?

Hope it explains it a little more

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

 

[Guest]

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

 

[Mike Hyndman]

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

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