Counter to track number of times cell changes

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am trying to setup a worksheet that has three columns. In cell A2, it is a binary number that is auto-populated by the code; in B2, it is a counter that counts the number of times cell A2 changes; and in C2, I have an if/then formula that outputs a binary number based upon a cell in another worksheet. I've written the VBA code so that when cell C2 is 1 it triggers cell A2 to be filled with 1, and if not, it will be 0. Subsequently, whenever cell A2 is 1, cell B2 will count each instance and keep record of the number of times A2 has been 1. Unfortunately, it seems that cell B2 only recognizes that cell has changed to a 1 when I manually enter 1 in A2 and hit enter. Can someone help me understand why B2 isn't automatically recognizing when cell A2 automatically changes to a 1 (when the code auto-populates it based on the logic in C2)? Thanks so much!

VBA Code:
Private Sub worksheet_Activate()
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
    Application.EnableEvents = True
    Exit Sub
End Sub

Sub Macro2(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
    If UCase(Target) = 1 Then
        Range("B2").Value = Range("B2").Value + 1
    End If
End Sub
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi vw412, additionally wondering whether dropping code into the macro that activates Sheet2! A1, which cell C2's if/then statement relies on, would solve the problem? Any help is appreciated!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
So, good news. I was able to find a way around the problem with the counter not recognizing changes from the formula-based cell in C2. The code attached now allows for those changes to be detected through the use of a worksheet_calculate event. I'd like to now have the code do the same function it was doing for cells A2, B2, and C2 but for a range of data. The cells in column C will all be filled with formulas, same as before, that output 1 or 0 and auto-populate respective cell in column A with a 1 or 0 as well. Column B cells would count each time their respective A column cells change to a 1. How would I go about converting the code to work with a data range rather than specific cells? Thanks!

In the below code:
- Wherever C2 is would be C2:C10
- Wherever A2 is would be A2:A10
- And wherever B2 is would be B2:B10

VBA Code:
Private Sub Worksheet_Calculate()
On Error Resume Next
  If Range("C2").Value = 1 Then
    Range("A2") = "1"
  ElseIf Range("C2").Value = "0" Then
    Range("A2") = "0"
  End If
If Range("A2").Value = 1 Then
    Range("B2").Value = Range("B2").Value + 1
  End If
End Sub
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
*drth,

the if/then/else statement testing for C2 in the worksheet_change routine is never triggered if you are updating C2 with a formula. the Change event only fires when the user manually enters a value or if a macro changes the value. That is why I removed it. The problem you had with B2 incrementing when changing between the two sheets is solved by the Activate and Deactivate routines I provided. Just the process of changing sheets triggered the Change event routine, so checking the value of C2 before and after changing sheets handles that problem.

If there is something I missed, I will need more details to deal with it.

Above you talk about "the macro that activates Sheet2!A1". I never saw that macro and don't know what it is supposed to do. Whether it will change the process or not I can't answer without seeing it and how it interacts with the code I gave you.
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
*drth,
I will look at this a little more. I considered the Calculate event but felt it was "too big a hammer" to deal with the issue of C2 not raising Change event since it has formula. I made an assumption that since you were switching between Sheet1 and Sheet2 to update Sheet2!A1 then the Activate/Deactivate events on Sheet1 would suffice. If my assumption was not correct then we need to talk some more.

I noticed in your original code and now in the code you just sent that you are mixing strings and integers in your statements. You check C2 for the integer 1 then you check C2 for the string "0". Then you fill A2 with string "0" or string "1" but check if it is the integer 1. Is there some reason you are doing this?

I have writing VBA for a long time and though I am not an Excel MVP I have lots of experience in programming and VBA. I want to help people become better programmers so take my comments with that in mind.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi vw412. Good question and sorry for the confusion. The code should be using integers, not strings! Must have left string references in there as I was working on the code. Attaching a cleaned up code below. Still not sure how to modify the code to use it in ranges in the code...where C2 is would be C2:C10, where A2 is would be A2:A10, and where B2 is would be B2:B10. Any thoughts?

VBA Code:
Private Sub Worksheet_Calculate()
On Error Resume Next
  If Range("C2").Value = 1 Then
    Range("A2") = 1
  ElseIf Range("C2").Value = 0 Then
    Range("A2") = 0
  End If
If Range("A2").Value = 1 Then
    Range("B2").Value = Range("B2").Value + 1
  End If
End Sub
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
I have tested your code and modified for you latest expansion of problem. Here is my updated code:
VBA Code:
Option Explicit
Dim HoldC2Value(1 To 10) As Variant

Private Sub worksheet_Activate()
  Dim I As Long
  For I = 2 To 10
    If Cells(I, 3).Value <> HoldC2Value(I) Then
      Cells(I, 1) = Cells(I, 3).Value
    End If
  Next I
End Sub

Private Sub Worksheet_Deactivate()
  Dim I As Long
  For I = 2 To 10
    HoldC2Value(I) = Cells(I, 2).Value
  Next I
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
  If Target.Value = 1 Then
    Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 1
  End If
End If
End Sub

Private Sub Worksheet_Calculate()
  Dim I As Long
  For I = 2 To 10
    If Cells(I, 3).Value = 1 Then
      Cells(I, 1) = 1
    ElseIf Cells(I, 3).Value = 0 Then
      Cells(I, 1) = 0
    End If
  Next I
  For I = 2 To 10
    If Cells(I, 1).Value = 1 Then
      Cells(I, 2).Value = Cells(I, 2).Value + 1
    End If
  Next I
End Sub
once you put this code into your workbook, comment out the Activate and Deactivate routines and try the code. Then comment out the Calculate code and uncomment the Activate and Deactivate code. See which one does what you want it to. Personally I think the Calculate routine introduces additional problems that are not in the Activate/Deactivate code. Let me know what you find.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm finding that the Calculate event code works better for me; however, I'm running into a problem with the code. I've noticed that when one row is switched to 1, any other row that is already 1 will also have their counters incremented up. For example, if data in row 2 is switched to 1, and data in row 3 is already 1, then row 3's counter is incremented along with row 2's counter. Good news though is that any rows that are 0 do not have their counter's incremented up. Is there a way to stop this from happening? Providing code I'm using below.

VBA Code:
Private Sub Worksheet_Calculate()
  Dim I As Long
  For I = 2 To 10
    If Cells(I, 3).Value = 1 Then
      Cells(I, 1) = 1
    ElseIf Cells(I, 3).Value = 0 Then
      Cells(I, 1) = 0
    End If
  Next I
  For I = 2 To 10
    If Cells(I, 1).Value = 1 Then
      Cells(I, 2).Value = Cells(I, 2).Value + 1
    End If
  Next I
End Sub
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
That is why I suggested you use the Activate/Deactivate code. Your Calculate code does not detect a change in the value in A; it only looks to see if it is 1 right now, not if it changed to 1. Every time the Calculate routine runs it will increment if the A value is 1 regardless.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Got it. Makes sense. I used the Activate and Deactivate routines; however the code doesn't seem to be working for me. Pasting what I'm using below. I'm finding that when the formula in column C becomes a 1 or 0, it isn't triggering the code to auto-populate column A with a 1 or 0, and in-turn triggering a count in Column B if A is 1. Any ideas?

VBA Code:
Option Explicit
Dim HoldC2Value(1 To 10) As Variant

Private Sub worksheet_Activate()
  Dim I As Long
  For I = 2 To 10
    If Cells(I, 3).Value <> HoldC2Value(I) Then
      Cells(I, 1) = Cells(I, 3).Value
    End If
  Next I
End Sub

Private Sub Worksheet_Deactivate()
  Dim I As Long
  For I = 2 To 10
    HoldC2Value(I) = Cells(I, 2).Value
  Next I
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
  If Target.Value = 1 Then
    Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 1
  End If
End If
End Sub
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Got it. Makes sense. I used the Activate and Deactivate routines; however the code doesn't seem to be working for me. Pasting what I'm using below. I'm finding that when the formula in column C becomes a 1 or 0, it isn't triggering the code to auto-populate column A with a 1 or 0, and in-turn triggering a count in Column B if A is 1. Any ideas?

VBA Code:
Option Explicit
Dim HoldC2Value(1 To 10) As Variant

Private Sub worksheet_Activate()
  Dim I As Long
  For I = 2 To 10
    If Cells(I, 3).Value <> HoldC2Value(I) Then
      Cells(I, 1) = Cells(I, 3).Value
    End If
  Next I
End Sub

Private Sub Worksheet_Deactivate()
  Dim I As Long
  For I = 2 To 10
    HoldC2Value(I) = Cells(I, 2).Value
  Next I
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A10")) Is Nothing Then
  If Target.Value = 1 Then
    Cells(Target.Row, 2).Value = Cells(Target.Row, 2).Value + 1
  End If
End If
End Sub
Would also like to add that column C cells are formula-based.
 

Forum statistics

Threads
1,141,301
Messages
5,705,583
Members
421,400
Latest member
chakam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top