On a worksheet:

In cell A1 is a number.
In cell B1 is another number.
In cell C1 is the formula: = sum(A1+B1)

The values in A1 and B1 are downloaded from the web and change constantly.

What I want to do is to use cell D1 to accumulate the values of C1.

For example
A1 = 100, B1 = 200, C1 = 300, D1 = 300
A1 = 200, B1 = 300, C1 = 500, D1 = 300 + 500 = 800, etc.

Presumably, I can use to a worksheet change event to capture the changes in
C1 to put them in D1. The problem is C1 depends on A1 and B1. Thus, when
there is a new value in A1, C1 changes. Then when there's a new value in B1,
C1 changes again. So the value in A1 will be double counted. How can I make
sure that the worksheet change event only fires when both A1 and B1 have new
values?

TIA.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:B1"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Range("D1").Value = Range("D1").Value + Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Shatin" wrote in message
...
On a worksheet:

In cell A1 is a number.
In cell B1 is another number.
In cell C1 is the formula: = sum(A1+B1)

The values in A1 and B1 are downloaded from the web and change constantly.

What I want to do is to use cell D1 to accumulate the values of C1.

For example
A1 = 100, B1 = 200, C1 = 300, D1 = 300
A1 = 200, B1 = 300, C1 = 500, D1 = 300 + 500 = 800, etc.

Presumably, I can use to a worksheet change event to capture the changes
in
C1 to put them in D1. The problem is C1 depends on A1 and B1. Thus, when
there is a new value in A1, C1 changes. Then when there's a new value in
B1,
C1 changes again. So the value in A1 will be double counted. How can I
make
sure that the worksheet change event only fires when both A1 and B1 have
new
values?

TIA.

Shatin wrote:
On a worksheet:

Hello Shatin,
the <Change Event occurs only when cells on the worksheet are changed
by the user
The <Worksheet_Calculate event occurs after the worksheet is recalculated.
So if you disable automatic calculation, you can use the
<Worksheet_Calculate event to update cell 'D1'
But you have to disable automatic calculation , otherwise values are
double counted.

Private Sub Worksheet_Calculate()
Range("D1").Value = Range("D1").Value + Range("C1").Value
End Sub


Mike, from Luxembourg

You could keep 2 public variables, say A1Ref and B1Ref, and on the
worksheet change event have something like this

if range("A1").value < A1Ref AND range("B1").value < B1Ref then
range("D1").value = range("A1").value + range("B1").value
A1Ref = range("A1").value
B1Ref = Range("B1").value
End If

Although the problem with this is if A1 changes twice before B1 changes
or vice versa, would this happen?

Thanks for your input Mike.
Isn't another piece to this the fact that the OP needs to also know that if
he changes Calculation mode "from Automatic - via Tools, Options to
Manual" - this is going to affect ALL other Excel workbooks!! so, in this
same specific file it would probably be best to create an Workbook_Open
event making the change Calc from Automatic to Manual and also a
Workbook_Close event to reverse setting of Calc from Manual to Automatic.
PLUS - he'll need to remember (all the time the file is open that any other
Workbooks he works with will **AT THE TIME** also have Calc set to MANUAL,,,

Right?
Please clarify anything I've stirred up here, Tks...
Jim


"Mike" wrote in message
...
Shatin wrote:
On a worksheet:

Hello Shatin,
the <Change Event occurs only when cells on the worksheet are changed by
the user
The <Worksheet_Calculate event occurs after the worksheet is
recalculated.
So if you disable automatic calculation, you can use the
<Worksheet_Calculate event to update cell 'D1'
But you have to disable automatic calculation , otherwise values are
double counted.

Private Sub Worksheet_Calculate()
Range("D1").Value = Range("D1").Value + Range("C1").Value
End Sub


Mike, from Luxembourg

I think Bob gave you the best answer, but if you want to specifically wait
until both A1 and B1 have changed, I think this will work as well.

Private Sub Worksheet_Change(ByVal Target As Range)
Static bA1Change as Boolean
Static bB1Change as Boolean

If Target.count 1 then exit sub
If Target.Address < "$A$1" and _
Target.Address < "$B$1" Then exit sub

If Target.Address = "$A$1" then _
bA1Change = True
If Target.Address = $B$1" then _
bB1Change = True
if bA1Change and bB1Change then
On Error GoTo ErrHandler
Application.EnableEvents = False
Range("D1").Value = Range("D1").Value + Range("C1").Value
bA1Change = False : bB1Change = False
End If

ErrHandler:
Application.EnableEvents = True
End Sub

This assumes that whatever updates A1 and B1 fires a change event.

--
Regards,
Tom Ogilvy


"Shatin" wrote in message
...
On a worksheet:

In cell A1 is a number.
In cell B1 is another number.
In cell C1 is the formula: = sum(A1+B1)

The values in A1 and B1 are downloaded from the web and change constantly.

What I want to do is to use cell D1 to accumulate the values of C1.

For example
A1 = 100, B1 = 200, C1 = 300, D1 = 300
A1 = 200, B1 = 300, C1 = 500, D1 = 300 + 500 = 800, etc.

Presumably, I can use to a worksheet change event to capture the changes
in
C1 to put them in D1. The problem is C1 depends on A1 and B1. Thus, when
there is a new value in A1, C1 changes. Then when there's a new value in
B1,
C1 changes again. So the value in A1 will be double counted. How can I
make
sure that the worksheet change event only fires when both A1 and B1 have
new
values?

TIA.

Thanks to all for you help!