I have VBA code that does the following. In Excel Cell C2 and D2 keep a count. As I enter a value in D2 it adds C2 and D2 together and places the Sum in C2. If I reset D2 to zero C2 keeps it's value and when I enter a new value in D2, this cell now starts a new count and again adds it to C2. Example: C2 = 0 and D2 = 0. Now I enter 1 in D2 so C2 = 1. Now I enter 2 in D2, D2 now = 3 and so does C2. Now if I enter 0 in D2 in order to start a new count C2 does not change, in this case C2 will still have a value of 3. So, at this point C2 = 3 and D2 now = 0. Now If I add 1 to D2 it will now = 1 and C2 will = 4. The VBA code I have works fine, my problem is how do I get it to do this in Cells C2 to C33 and D2 to D33? So, the value of C2 and D2 go together, C3 and D3 together and so on.
I tried this code and it works fine but only in cells C2 and D2.
Dim mRangeNumericValue As Double
'Updated by ExtendOffice 20180814
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EndF
Application.EnableEvents = False
Dim d As Double
d = Range("C2").Value
Range("C2") = d + Range("D2").Value
If Not Application.Intersect(Target, Range("D2")) Is Nothing Then
If Target.Count = 1 Then
If (Len(Target.Range("A1").Value) \> 0) And IsNumeric(Target.Range("A1").Value) Then
If Target.Range("A1").Value = 0 Then mRangeNumericValue = 0
Target.Range("A1").Value = 1 \* Target.Range("A1").Value + mRangeNumericValue
End If
End If
End If
EndF:
Application.EnableEvents = True
mRangeNumericValue = 0
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo err0
If Target.Count = 1 Then
If (Len(Target.Range("A1").Value) \> 0) And IsNumeric(Target.Range("A1").Value) Then
mRangeNumericValue = Target.Range("A1").Value
End If
End If
err0:
End Sub