Keep adding to one cell to carry a total in another

  • Thread starter Thread starter Mex
  • Start date Start date
M

Mex

Hi
I want to be able to use one cell to keep adding new numbers to that will
accumulate the total in another cell.
Ex: I use A1 as the cell to input a new number that will carry a total in A2

any help would be appreciated
 
Try this in the sheet module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target <> Range("A1") Then
Range("A1").Select
Exit Sub
End If

Application.EnableEvents = False
Range("A2").Value = Range("A1").Value + Range("A2").Value
Target.Select
Application.EnableEvents = True

End Sub

HTH
Regards,
Howard
 
Hi Howard

I'm sure what you have provide will be helpful unfortunately I really am a
beginner and I’m not sure where I should actually be entering this. I thank
you for your patience and possibly a little more information.

Thanks
 
Right click sheet tab>view code>copy/paste this.
Now when you put a number in cell a1 cell a2 will increase by that number.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
End Sub
 
AWESOME, thanks Don that did the trick. Now I’m wondering how to do it again
on 2 different columns for example K2 & P2 on the same sheet. Is there a way
to just add to this formula or does it have to be added separately? I tried
adding it below the original one you provided but it keeps coming up with the
error message: “ambiguous mane detected worksheet_changeâ€

Thanks you have just saved me an incredible amount of time. ïŠ


Don Guillett said:
Right click sheet tab>view code>copy/paste this.
Now when you put a number in cell a1 cell a2 will increase by that number.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mex said:
Hi
I want to be able to use one cell to keep adding new numbers to that will
accumulate the total in another cell.
Ex: I use A1 as the cell to input a new number that will carry a total in
A2

any help would be appreciated
Click to expand...
Click to expand...
 
Add it INSIDE the change event. There can only be ONE per tab.
Private Sub Worksheet_Change(ByVal target As Excel.Range)

If target.Address = "$A$1" Then
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
end if

If target.Address = "$P$1" Then
Application.EnableEvents = False
[p2] = target.Value + [p2]
Application.EnableEvents = True
end if

'etc
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mex said:
AWESOME, thanks Don that did the trick. Now I’m wondering how to do it
again
on 2 different columns for example K2 & P2 on the same sheet. Is there a
way
to just add to this formula or does it have to be added separately? I
tried
adding it below the original one you provided but it keeps coming up with
the
error message: “ambiguous mane detected worksheet_changeâ€

Thanks you have just saved me an incredible amount of time. ïŠ


Don Guillett said:
Right click sheet tab>view code>copy/paste this.
Now when you put a number in cell a1 cell a2 will increase by that
number.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mex said:
Hi
I want to be able to use one cell to keep adding new numbers to that
will
accumulate the total in another cell.
Ex: I use A1 as the cell to input a new number that will carry a total
in
A2

any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
Even better.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If Not Intersect(target, Range("a1,k1,p1")) Is Nothing Then
Application.EnableEvents = False
target.Offset(1) = target.Offset(1) + target
Application.EnableEvents = True
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don Guillett said:
Add it INSIDE the change event. There can only be ONE per tab.
Private Sub Worksheet_Change(ByVal target As Excel.Range)

If target.Address = "$A$1" Then
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
end if

If target.Address = "$P$1" Then
Application.EnableEvents = False
[p2] = target.Value + [p2]
Application.EnableEvents = True
end if

'etc
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Mex said:
AWESOME, thanks Don that did the trick. Now I’m wondering how to do it
again
on 2 different columns for example K2 & P2 on the same sheet. Is there a
way
to just add to this formula or does it have to be added separately? I
tried
adding it below the original one you provided but it keeps coming up with
the
error message: “ambiguous mane detected worksheet_changeâ€

Thanks you have just saved me an incredible amount of time. ïŠ


Don Guillett said:
Right click sheet tab>view code>copy/paste this.
Now when you put a number in cell a1 cell a2 will increase by that
number.

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address <> "$A$1" Then Exit Sub
Application.EnableEvents = False
[a2] = target.Value + [a2]
Application.EnableEvents = True
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi
I want to be able to use one cell to keep adding new numbers to that
will
accumulate the total in another cell.
Ex: I use A1 as the cell to input a new number that will carry a total
in
A2

any help would be appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top