Counter for Cell value that is always changing between two states

PTamiggi

New Member
Joined
May 2, 2021
Messages
6
Office Version
  1. 2013
Platform
  1. Windows
Okay I am just starting out with VBA and I'm lost. So I have a single cell that has the following formulas =IF(Car_Weight>=70,"Yes",""). I need a counter that counts up only the amount of times the cell reads "Yes". This counter would also need a reset to 0 button. Not sure if this enough info enable someone to help. I've tried the following VBA code but it counts every switch between "yes" and "".
VBA Code:
Dim xCount As Integer
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xRg As Range, xCell As Range
    On Error Resume Next
    If Target = Range("B9") Then
        xCount = xCount + 1
        Range("C9").Value = xCount                                    
    End If
    Application.EnableEvents = False
    Set xRg = Application.Intersect(Target.Dependents, Me.Range("B9"))
    If Not xRg Is Nothing Then
        xCount = xCount + 1
        Range("C9").Value = xCount
    End If
    Application.EnableEvents = True
End Sub

This is my first post on here and first time reaching out for help like this. Thank you all in advance
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,355
Office Version
  1. 2013
Platform
  1. Windows
Google / YouTube are your friend for finding tutorial videos.

Developer Tab. > Insert > ActiveX > Command Button > Draw Button on Sheet
Right Click button > View Code
That will take you to the Command Button's Click event code which will be Empty for a new button.
eg.
VBA Code:
Private Sub CommandButton1_Click()
'Here you can either call the Re-set sub as per
Call Re_Set
'OR
'You just post the body of the Re-Set sub in here, instead
End Sub
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Forum statistics

Threads
1,141,062
Messages
5,704,060
Members
421,325
Latest member
tapete86

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