I would like to know how to set my variable Cont to 0 after every click of a button in a different macro(recorded).

[shulman]

My code is from a website online. All that it is supposed to do is add 1 to the value of E2 every time the user changes A2. I wanted it to reset E2 to zero every time the user clicked a button.

Dim Cont As Integer
'Integer means no decimals
 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rnge As Range, Cll As Range
On Error Resume Next
If Target = Range("A2") Then
Cont = Cont + 1
'If the user clicked this cell then the variable "Cont" will change to Cont+1
       Range("E2").Value = Cont
'The cell E2 needs updating to the new value
End If
'EXPERIMENTAL: If Button is clicked End If statement & clear E2

   Application.EnableEvents = False
   Set Rnge = Application.Intersect(Target.Dependents, Me.Range("A2"))
   If Not Rnge Is Nothing Then
        Cont = Cont + 1
        Range("E2").Value = Cont
   End If
    Application.EnableEvents = True
End Sub

 

[Joe4]

Welcome to the Board!

If I understand you correctly, you have a lot of unnecessary stuff there.

Here is all that you need for your Worksheet_Change event procedure code to increase the value in E2 by 1 every time A2 is updated:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        Range("E2").Value = Range("E2").Value + 1
    End If

End Sub

And then to reset the value in E2 to 0, all you need is this one line code:

Sub MyClearValue()
    Range("E2") = 0
End Sub

and then assign that code to your button.

 

[shulman]

I want the macro to only record changes to cell E2 when cell A2 is has a changed value. I don't want the number to update in E2 every time I delete what was in A2. Also I want the two (A2 and E2) to change to zero at the button click if possible.

 

[Joe4]

I want the macro to only record changes to cell E2 when cell A2 is has a changed value. I don't want the number to update in E2 every time I delete what was in A2.

OK, this amendment will ignore it if the change results A2 being cleared:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A2")) Is Nothing Then
        If Range("A2") <> "" Then Range("E2").Value = Range("E2").Value + 1
    End If

End Sub

Also I want the two (A2 and E2) to change to zero at the button click if possible.

I bet you could have figured this one out yourself! It is simply a matter of adding another like very similar to the first:

Sub MyClearValue()
    Range("A2") = 0
    Range("E2") = 0
End Sub