Increment value of cell if another cell changes value

denisined12

New Member
Joined
Oct 9, 2019
Messages
6
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
Hello,

You could test the following

Code:
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

New Member
Joined
Oct 9, 2019
Messages
6
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,493
Office Version
365
Platform
Windows
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

New Member
Joined
Oct 9, 2019
Messages
6
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
You could test the following

Code:
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

New Member
Joined
Oct 9, 2019
Messages
6
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

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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 ...
 

Forum statistics

Threads
1,082,305
Messages
5,364,401
Members
400,800
Latest member
germanpbv

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top