Hi All!
This is my delema:
A1 contains a number (say 3)
Following is what I am trying to achieve:
A2 reflects A1 BUT when A1 is changed to 2, then A2 must Show the total of
A1 before(3) + A1 now(2) so A2 must show 5.
I tried using another row to enter the new data but no joy.
May be someone can help me with a macro that when A1 is clicked (to enter
the new value) the old value is stored in a variable and passed on to A2
for adding up.
I hope I made myself understood.
Thanks
Regards
bahri

Hello,

Put this code in the skeet code:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not (Application.Intersect(Range("A1"), Target) Is Nothing) Then
Range("A2").Value = Range("A2").Value + Range("A1").Value
End If
Application.EnableEvents = True
End Sub

hope it will help you

"bahri" a écrit dans le message de
...
Hi All!
This is my delema:
A1 contains a number (say 3)
Following is what I am trying to achieve:
A2 reflects A1 BUT when A1 is changed to 2, then A2 must Show the total of
A1 before(3) + A1 now(2) so A2 must show 5.
I tried using another row to enter the new data but no joy.
May be someone can help me with a macro that when A1 is clicked (to enter
the new value) the old value is stored in a variable and passed on to A2
for adding up.
I hope I made myself understood.
Thanks
Regards
bahri

You could turn calculation to manual (and use a circular reference), but that
always scares me.

Instead, you could use an event macro like the one JE McGimpsey shares:
http://mcgimpsey.com/excel/accumulator.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

bahri wrote:

Hi All!
This is my delema:
A1 contains a number (say 3)
Following is what I am trying to achieve:
A2 reflects A1 BUT when A1 is changed to 2, then A2 must Show the total of
A1 before(3) + A1 now(2) so A2 must show 5.
I tried using another row to enter the new data but no joy.
May be someone can help me with a macro that when A1 is clicked (to enter
the new value) the old value is stored in a variable and passed on to A2
for adding up.
I hope I made myself understood.
Thanks
Regards
bahri

--

Dave Peterson

Hi All!
Have not tried anything yet I just saw the quick response, seems OK.
I Cannot Thank you all enough. Great!!

Regards
bahri
=================

"Dave Peterson" wrote in message
...
You could turn calculation to manual (and use a circular reference), but
that
always scares me.

Instead, you could use an event macro like the one JE McGimpsey shares:
http://mcgimpsey.com/excel/accumulator.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros he
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

bahri wrote:

Hi All!
This is my delema:
A1 contains a number (say 3)
Following is what I am trying to achieve:
A2 reflects A1 BUT when A1 is changed to 2, then A2 must Show the total
of
A1 before(3) + A1 now(2) so A2 must show 5.
I tried using another row to enter the new data but no joy.
May be someone can help me with a macro that when A1 is clicked (to enter
the new value) the old value is stored in a variable and passed on to A2
for adding up.
I hope I made myself understood.
Thanks
Regards
bahri

--

Dave Peterson

Hi
I have tried a few things out.
Works great on one instance that is applied to one row and combining for two
rows as below for rows 1 and 2:
==================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With

With Target
If .Address(False, False) = "A2" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B2").Value = Range("B2").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
=====================
However is there a way to apply the macro for several rows without producing
such long code?
Thanks all

Regards
bahri

So you're putting the accumulator in column B (not in the row below), right?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time!
End If

'change the range to check here
If Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Exit Sub
End If

With Target
If IsNumeric(.Value) Then
If IsNumeric(.Offset(0, 1).Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

bahri wrote:

Hi
I have tried a few things out.
Works great on one instance that is applied to one row and combining for two
rows as below for rows 1 and 2:
==================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With

With Target
If .Address(False, False) = "A2" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B2").Value = Range("B2").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
=====================
However is there a way to apply the macro for several rows without producing
such long code?
Thanks all

Regards
bahri

--

Dave Peterson

Hi!
That works Great
Thanks

Regs
bahri
"Dave Peterson" wrote in message
...
So you're putting the accumulator in column B (not in the row below),
right?

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Cells.Count 1 Then
Exit Sub 'one cell at a time!
End If

'change the range to check here
If Intersect(Target, Me.Range("A1:A10")) Is Nothing Then
Exit Sub
End If

With Target
If IsNumeric(.Value) Then
If IsNumeric(.Offset(0, 1).Value) Then
Application.EnableEvents = False
.Offset(0, 1).Value = .Offset(0, 1).Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub

bahri wrote:

Hi
I have tried a few things out.
Works great on one instance that is applied to one row and combining for
two
rows as below for rows 1 and 2:
==================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Address(False, False) = "A1" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B1").Value = Range("B1").Value + .Value
Application.EnableEvents = True
End If
End If
End With

With Target
If .Address(False, False) = "A2" Then
If IsNumeric(.Value) Then
Application.EnableEvents = False
Range("B2").Value = Range("B2").Value + .Value
Application.EnableEvents = True
End If
End If
End With
End Sub
=====================
However is there a way to apply the macro for several rows without
producing
such long code?
Thanks all

Regards
bahri

--

Dave Peterson