CumSum a single Cell that is dynamically changing

[wnakhoul]

Hi All,

I have the following situation in Excel and hope that someone can post a clean VBA solution to it (I am guessing a Worksheet_Calculate procedure after declaring some DIMs and CONSTs, Option Explicit, Option Base 1, Option Compare Text, DisableEvents etc...).
The situation is as below (hope to explain it as clearly and accurately as possible to facilitate the solution):

* I have a open excel worksheet (Call it worksheet A)
* One cell in Worksheet A (call it Cell X) has a formula
* Cell X formula is linked to another formula which is ultimately linked to an external data feed.
* As a result of the external data feed and as long as the data feed connection is ON, Cell X is dynamically changing throughout the day.
* Cell X value changes fluctuate between 0, positive and negative numbers (whole integer numbers i.e. no decimals. Example, 0, 803, -3,205, 0, 10,502 etc...)

Having described the situation above, I am seeking a VBA Private Sub Worksheet_Calculate() procedure that:

* CumSum all the positive value changes in once Cell (call it Cell Y). Cell Y can be in worksheet A or a separate worksheet.
* CumSum all the negative value changes in once Cell (call it Cell Z). Cell Z can be in worksheet A or a separate worksheet.

Many thanks in advance.

 

[Peter_SSs]

Welcome to the MrExcel board!

Is it just something like this pseudo code for the Worksheet Calculate event?

If cellX.Value > 0 Then
  cellY.Value = cellY.Value + cellX.Value
Else
  cellZ.Value = cellZ.Value + cellX.Value
End If

 

[wnakhoul]

Welcome to the MrExcel board!

Is it just something like this pseudo code for the Worksheet Calculate event?

If cellX.Value > 0 Then
  cellY.Value = cellY.Value + cellX.Value
Else
  cellZ.Value = cellZ.Value + cellX.Value
End If

Thank you Peter. I have other events causing changes to cells in the worksheet. I want the above code to trigger when the subject cell changes in the worksheet. Should I precede your above code with:

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Sheets("sheet1").Range("CellX")
If Not Intersect(target, Sheets("sheet1").Range("CellX")) Is Nothing Then
If cellX.Value > 0 Then
cellY.Value = cellY.Value + cellX.Value
Else
cellZ.Value = cellZ.Value + cellX.Value
End If
End Sub

Would that be the complete code you think?

 

[Peter_SSs]

Set target = Sheets("sheet1").Range("CellX")
If Not Intersect(target, Sheets("sheet1").Range("CellX")) Is Nothing Then

No, that won't work because it says ..
If Not Intersect(Sheets("sheet1").Range("CellX"), Sheets("sheet1").Range("CellX")) Is Nothing Then
.. and that bold part will never be Nothing.

What is the formula in CellX?

You said CellX is in Worksheet A, what worksheet does the data feed go into? Could you use the Worksheet_Change event in that worksheet to determine when to do the cumulative value in Worksheet A?

 

[wnakhoul]

No, that won't work because it says ..
If Not Intersect(Sheets("sheet1").Range("CellX"), Sheets("sheet1").Range("CellX")) Is Nothing Then
.. and that bold part will never be Nothing.

What is the formula in CellX?

You said CellX is in Worksheet A, what worksheet does the data feed go into? Could you use the Worksheet_Change event in that worksheet to determine when to do the cumulative value in Worksheet A?

The data feed goes is connected through an Add-in. When I log in through the Add-in, yes I normally have Worksheet A active and Cell X is in Worksheet A. But Worksheet A VBA already has a complicated VBA procedure written in it so I dont want to tamper or add to that code. I prefer to either write the sub private procedure in another clean and blank worksheet B (triggering by when Cell X in worksheet A changes value or I can bring Cell X value across to blank Worksheet B by simple = formula and then trigger same when this cell value changes.

 

[wnakhoul]

The data feed goes is connected through an Add-in. When I log in through the Add-in, yes I normally have Worksheet A active and Cell X is in Worksheet A. But Worksheet A VBA already has a complicated VBA procedure written in it so I dont want to tamper or add to that code. I prefer to either write the sub private procedure in another clean and blank worksheet B (triggering by when Cell X in worksheet A changes value or I can bring Cell X value across to blank Worksheet B by simple = formula and then trigger same when this cell value changes.

Formula is Cell X is =LOOKUP(2,1/(Monitor!C3:Monitor!C151<>""),Monitor!C3:Monitor!C151)
Is monitoring column C in another worksheet and returning the 0, + or - values that I want to cumsum separately (+ves in one cell and -ves in anotehr cell). Assume 0 is positive, it doesn't affect anything.

 

[Peter_SSs]

In sheet 'Monitor' do cells C3:C151 contain formulas or are they populated directly by the data feed?

If formulas, what is the formula in C3 of 'Monitor'?

 

[wnakhoul]

C3 has a simple IF formula =IF(B3="","",INT((B3-B2)*$D$1))

 

[Peter_SSs]

I'm trying to track down where in this chain of cells/formulas, the data feed gets used. Seems it is slow progress this way ... back to my earlier question:

what worksheet does the data feed go into?

 

[wnakhoul]

I really think this is not relevant mate. The data feeds into numerous sheets in the workbook. At the end of the day, there is a cell in a workbook that is dynamically changing as a result of the data feed throughout the multiple worksheets and I am trying to write a code that can separate every number change into 3 baskets (negative, 0 and positive). Then CumSum the positives including 0 in one cell and the negatives in another cell. Hope you can help me with that Pete.