Increment value of cell if another cell changes value

[denisined12]

Hello,
Is there a way in Macros or VBA to increment the value of a cell by 1 if another cell changes value?

Ill give the example:

In cells A1 to A56 I have links to another workbook that update every few seconds. I would like to count in Column B -- B1 to B56 -- The changes from each cell.
A1 updates its value 6 times next minute -- B1 displays 6
A2 updates its value 11 times next minute -- B2 displays 11

If anyone could help me I would very much appreciate it!
I really hope I made myself clear in explanation.

With respect,
Denis.

 

[James006]

Hello,

You could test the following

Private Sub Worksheet_Change(ByVal Target As Range)
' Display Nb of Changes
    Application.EnableEvents = False
       Target.Offset(0, 1) = Target.Offset(0, 1) + 1
    Application.EnableEvents = True
End Sub

Hope this will help

 

[denisined12]

Hello James,

Unfortunately this is not working. The values increment only if I click in A cells and than exit. If the values change by themselves no increment is done in B column.



Any suggestions?

 

[denisined12]

And thank you for your fast response!

 

[Joe4]

The issue is that changes due to formulas/linked cells do NOT trigger a Worksheet_Change event procedure. Those are only triggered when a change is manually made to a cell (i.e. direct cell entry or copy/paste).
I am not sure if a Worksheet_Calculate event procedure would work instead, but you could try it and find out.
In my mind, the best place to track the number of changes would be in the Source Workbook itself. That might depend on how they get updated there.

 

[denisined12]

Thank you Joe,

Do you have any ideas how this code would look like? I have very limited knowledge on vba coding. I tried myself to change the procedure in the previous code but aint working.


Denis.

 

[James006]

You could test the following

Private Sub Worksheet_Calculate()
Dim OldVal As Variant, NewVal As Variant
  Application.EnableEvents = False
    NewVal = ActiveCell.Value
    Application.Undo
    OldVal = ActiveCell.Value
    If OldVal <> NewVal Then ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value + 1
  Application.EnableEvents = True
End Sub

Hope this will help

 

[denisined12]

Still not doing so.

If it helps, I put the links in the same workbook, in another sheet, sheet2, but still updating from sheet 1. The source in sheet 1 A column are mt4 spot prices and updated with command: ='MT4'|Bid!EURUSD
Could this be the problem?

 

[James006]

No ... it is not the poblem ...

The issue is the live feed ...

Are you using a Reuters or Bloomberg feed ...?

You might have built-in trackers/counters for price changes ...

 

[denisined12]

The feed is straight from meta trader 4 terminal.