Record Change in Value from a live feed Cell

[jk64]

I need VBA cod thatrecords the number of times a cell in a worksheet has a value change.

For example if cell J5 contains the live feed, if thatl value is changed to this would be recorded as 1 in the cell using the recording function. If that Cell value is then changed to 11, the cell change would be recorded as 2.

I'm really new to all this code stuff so help would be very much aprreciated.

I've tried using this and lsight variations, but none have worked:

Private Sub Worksheet_Calculate()
Static OldVal As Variant
With Range("B3")
    Application.EnableEvents = False
    If .Value <> OldVal Then
        .Offset(, 4).Value = Target.Offset(, 4).Value + 1
    End If
    Application.EnableEvents = True
End With

 

[VoG]

If your feed triggers a calculate event then it would be

Private Sub Worksheet_Calculate()
Static OldVal As Variant
With Range("B3")
    If .Value <> OldVal Then
        .Offset(, 4).Value = .Offset(, 4).Value + 1
        OldVal = .Value
    End If
End With

 

[jk64]

Hey Peter,

Thanks for your time.

I tired the code you posted and it didn't work. Maybe the live feed isnt a calculation event.

What i'm feeding is in bloomberg quotes. The cell function starts with =BDP(.....) where the dots are just locating the quotes in Blmbrg.

Any other suggestions?

 

[VoG]

Maybe it will trigger a change event. change B3 to the cell that receives the feed

Private Sub Worksheet_Change(ByVal Target As Range)
Static OldVal As Variant
If Target.Address(False, False) = "B3" Then
    With Target
        If .Value <> OldVal Then
            .Offset(, 4).Value = .Offset(, 4).Value + 1
            OldVal = .Value
        End If
    End With
End If
End Sub

 

[jk64]

Sorry Peter, but that didn't work either.

A couple of thoughts:
- Maybe it doent recognize a change becasue the actual contents of the cell arn't changing. The cell in the formula bar always reads "=BDP(...)", but the value projected in the cell is an interger changes in real time.
-Should I input in the place of B3 the actualy contents of the cell, i.e. the "=BDP(...)" stuff?

Suggestions?

 

[VoG]

Try entering this formula in a remote cell like Z1

=B3

then try the code that I posted in post #2.

 

[jk64]

Tried it and still nothing.
I'm assuming that you wanted me to put Z1 into the code. If that wasn't the case let me know.

 

[VoG]

Sorry no I meant to use the exact code here http://www.mrexcel.com/forum/showpost.php?p=2763590&postcount=2

If B3 changes then Z1 should recalculate and trigger the event code.

 

[jk64]

Did exactly what you said, but unfortunatly it didn't work either.

 

[VoG]

In that case, sorry but I don't know.