Impossible with formula BUT

  • Thread starter Thread starter bahri
  • Start date Start date
B

bahri

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
 
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 here:
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.)
 
Hi All!
Have not tried anything yet I just saw the quick response, seems OK.
I Cannot Thank you all enough. Great!!

Regards
bahri
=================
 
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
 
Hi!
That works Great
Thanks

Regs
bahri
Dave Peterson said:
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
Click to expand...
 
Back
Top