adding formula results together

[Trussy1]

I have a piece of software that has an excel link function which is fine. But I have a column of cells which have constantly changing values in. I need to add these values together to keep a running total. I am pretty sure the values are not the result of formulas more likely to be the way the software is fetching teh data through an API from the web. Bit like watching share prices change.

Here's hoping and thanks to anyone wishing to take on this headache.

Many thanks

Trussy1

 

[Ruddles]

In that case the code needs to go in the code module for the sheet where the data is coming in, i.e. Sheet1.

Paste this into the code module for Sheet1 and make sure there's no code in Sheet2:-

[FONT=Fixedsys]Option Explicit[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]Private Sub Worksheet_Change(ByVal Target As Range)
 
  Dim rDataRange As Range
  Dim rCell As Range
 
  Set rDataRange = Range("C2:C20")
 
  If Not Intersect(Target, rDataRange) Is Nothing Then
    For Each rCell In Intersect(Target, rDataRange)
      With Sheets("Sheet2").Cells([COLOR=blue]rCell.Row, "E"[/COLOR])
        .Value = .Value + rCell.Value
      End With
    Next rCell
  End If
 
End Sub[/FONT]

It checks the data coming in to Sheet1!C2:C20 and aggregates it directly into Sheet2!E2:E20 (the code in blue). It doesn't refer to Sheet2!D2:D20 at all - it doesn't need to.

See how that goes.

 

[Trussy1]

That works brilliantly Ruddles. Could I impose a little more. Its entirely my fault. Its something I did'nt anticipate. Imagine I was tracking a shares price on the Dow Jones, (this is the easiest way I can think of explaining it), from 9-10 am. Everything is working fine and I can see the running totals on that share. At 10am I swap over to look at another share on the Footsie, the incoming values from the Footsie keep adding onto the currrent values from thye Dow Jones. Is there any way that the code can incorporate something like when cell XX=1, reset the running totals to 0 and start again.

Entirely my fault Ruddles and brilliantly done

Many thanks

Trussy

 

[Ruddles]

That doesn't seem completely impossible... I'm just checking in to the forum before I disappear out of the house for a while, so I won't be able to look at this until a bit later today. Can you let me know what the 'trigger' would be for wanting the totals to reset? Is it when something else changes - some other cell in one of the worksheets, perhaps? When you say you're swapping over to a different source of data, how do you actually do that? What is it that happens in Excel at that moment? I mean, how does Excel know you've changed to a different data source? Does the name of the share or index appear somewhere in a worksheet?

At the very simplest you could add a command button which would reset those totals but I think it would be a little bit slicker if it could be made to happen automatically.

 

[Trussy1]

Good Morning Ruddles. Thanks again for a swift reply. Like you I also need to go out for a bit, so can I get in touch a little later. If needs be can I send you some screen shots via the private message mail? I can make life a little easier to explain that way.

Kind regards Trussy

Ratae Corieltauvorum
Britannia

 

[Trussy1]

Hi Ruddles. Thought I would send this off so you could look at it at your convienience. When cell P35=0, could that trigger a reset for the accumalative totals at all? Also I did notice, and again its my error not yours at all, your programming is working excellently and has'nt blipped once. The changing data values, although constantly changing, I notice sometimes may take a few seconds to update with the new value. The previous value remains in the cell untill updated. The accumalitive total I notice at times may add the 'static' value several times before the new data value appears in the cell. How difficult / feasible would it be to make each accumalative total add each data value once only?

Kind regards and I hope you had a good weekend

Trussy

 

[Ruddles]

When cell P35=0, could that trigger a reset for the accumalative totals at all?

Yes, it could trigger a reset each time it becomes zero. How will it do that? Manually? If so, a command button would be quicker and easier.

The changing data values, although constantly changing, I notice sometimes may take a few seconds to update with the new value. The previous value remains in the cell untill updated. The accumalitive total I notice at times may add the 'static' value several times before the new data value appears in the cell. How difficult / feasible would it be to make each accumalative total add each data value once only?

That shouldn't happen - the value should only be aggregated when the cell actually changes.

Would I be allowed to set the cell - the one in Sheet1!C2:C20 - to zero each time it's aggregated?

 

[Trussy1]

The cell P35 has a formula in it that I put in. Its an IF formula and will go to zero when a market closes before the next one commences.

As far as 0'ing the aggregated cells 0'away

did you check your private message box by the way?

Kind regards

Trussy