Excel formula

[petes]

Is there a way to enter a daily total into one column and have to
continually update the value? For example everyday I will come in and
put a value into C3 and D3 will continually update the total throughout
the month. Or even better if I could just put a number into C3 and it
would add it to the current value of C3.

Thanks in advance.

Chris

 

[mudraker]

Place this code on your worksheet module

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$C$3" Then
Exit Sub
ElseIf Not IsNumeric(Target.Value) Then
Exit Sub
End If
Range("d3") = Range("d3") + Range("c3")
End Sub

 

[petes]

worksheet module? Not sure where you mean. Sorr

 

[Frank Kabel]

Hi Chris
you can use the worksheet_change event to do this (Though I would not
recommend it, as you'll have problems to reset the value, etc.)
Put the following code in your worksheet module:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "C3" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("D3").Value = Range("D3").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

This will add up the values of C3 in D3

For an accumulator in the same cell use the following
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static accumulator As Double
With Target
If .Address(False, False) = "C3" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
accumulator = accumulator + .Value
Else
accumulator = 0
End If
Application.EnableEvents = False
.Value = accumulator
Application.EnableEvents = True
End If
End With
End Sub

for more information have a look at
http://www.mcgimpsey.com/excel/accumulator.html

HTH
Frank

 

[Frank Kabel]

Hi
1. rightclick on the tab name of the sheet in which you want this
accumulatio happen.
2. Choose 'Code'
3. Paste the code in the VBA editor
4. Close the editor
5. Save the workbook

HTH
Frank

 

[petes]

thank you that worked! Now I have one more problem. I need it t
calculate this from C3 to C(x). What part of the code do I need t
chnge for this to happen?

Thanks agai

 

[Frank Kabel]

Hi

for the code using columns C and D try the following

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 3 Then Exit Sub
With Target
If IsNumeric(.Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End With
End Sub

HTH
Frank

 

[petes]

I was hoping to use the single cell format. If it is not possible wit
the single cell format I can change. Sorry about that. Thanks i
advance.

Chri

 

[Frank Kabel]

The problem with the single cell format is that you have to store the
cell value in a static variable. Therefor the declaration of
Static accumulator As Double
This works fine for only one cell. But in you example you have to
create static variable for all rows (up to 65536). Never tried that
before, though you could declare an static array and use that as
accumulator per row. Use the following
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static accumulator(1 To 100) As Double
'If Target.Column <> 3 Then Exit Sub
If Intersect(Target, Me.Range("C1:C100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
With Target
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
accumulator(.Row) = accumulator(.Row) + .Value
Else
accumulator(.Row) = 0
End If
Application.EnableEvents = False
.Value = accumulator(.Row)
End With

CleanUp:
Application.EnableEvents = True
End Sub

I've restricted the accumulator for the first 100 rows. If you want it
for more rows change the check and the declaration of the static
accumulator 8I do not know if you'll run into memory troubles though)

HTH
Frank

 

[Gord Dibben]

Chris

You have been given some methods to achieve what you want but......

Have you given any thought to what happens if you enter an incorrect number?

How will you know if you have?

What steps to take to undo mistakes after they occur?

Gord Dibben Excel MVP