Constantly calculate and update the differences in a column of figures

[sherman51]

Hi, (I'm using Excel 2010)

I have a column of figures that are being constantly updated from a source that the spreadsheet is connected to. (In Column C)
In column D I want to display the difference when the figure changes compared to what it was previously, every time that it changes.

ie If C3 is 234.45 then changes to 256.42 then I would want D3 to display 21.97 (the difference), then when that changes to 259.34 I want D3 to then display 2.92 etc ...

The figures in column C may change many times per second

Thanks for any help
Paul

 

[James006]

Hi,

Are you familiar with Event macros ...?

Among several possibilities ... worksheet_change or worksheet_calculate ...

Hope this will help

 

[My Aswer Is This]

This statement here made me question how to do this:
You said:

The figures in column C may change many times per second

Wow having figures in a cell change many times per second.
A change event script may not be the best solution. But I do not have a better one.
That is a lot of changing to take place.

And what is the goal here. If the values change so fast what is the purpose of even changing them.
One split second the cell value reads 2.5 the next split second it reads 2.8 and on and on.

​

 

[sherman51]

Hi, thanks for your replies .. sorry for the delay ..

The spreadsheet is connected to a trading platform and the figures are fed at potentially 20ms, of course they will change when they change (so to speak) and it can be rapid at certain times.

The idea is to monitor a range and highlight certain activity ie largest changes ..

I have got as far as the code below which works fine manually .. ie
ie if I type 24 into H9, then 24 will appear in AN9, then if I change H9 to 26, then AN9 will display 2 ..etc this works fine
But If I connect the sheet to the trading software and lets say H9 shows 24, nothing will appear in AN9, and nothing will happen when H9 changes ..
It seems that the numbers displayed on a "Connected" sheet arn't recognised ..
as far as my knowledge goes it is fed via an API stream.

My vba and Excel is quite limited ...

Appreciate any help .. thanks

Option Explicit

Dim OldVal
Public Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("H9:H16")) Is Nothing Then
    Application.EnableEvents = False
    OldVal = Target.Value
    Application.EnableEvents = True
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H9")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN9") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H10")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN10") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H11")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN11") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H12")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN12") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H13")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN13") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H14")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN14") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H15")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN15") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
    If Not Intersect(Target, Range("H16")) Is Nothing Then
        Application.EnableEvents = False
        Range("AN16") = Target.Value - OldVal
        Application.EnableEvents = True
    End If
End Sub