Total and Average

[B Rossio]

I need help trying to track the total and average of different instances. The idea is to enter a value in F5, have it be added to the total in B5 (this keeps track of the total). After it adds the value to B5, it adds 1 to D33, this would keep track of the number of entries into F5 and then in turn updates the average. After both of these cells have been updated, F5 gets cleared (Range("F5").ClearContents). I've been having a heck of an issue when it comes to adding 1 to D33. For some reason it always adds 143 no matter what I do. Please help!

 

[offthelip]

Possibly, it depends on whether the cells you want to add the value into it is still just 4 columns to the left. if that is true then yes. If there is still some fixed relationship then you can just adjust the offset

 

[B Rossio]

Ok, before I try that. I tried to alter the Range("D33") = Range ("D33") + 1 to something more dynamic for when I do need to make another instance of the code with a different range. I tried replacing Range("D33") = Range ("D33") + 1 with Target.Offset(-2,-28) = Target.Offset(-2,-28) + 1 and I end up getting an error which I will try to attach to this comment.

Range("D33") = Range ("D33") + 1

 

[B Rossio]

Ok, before I try that. I tried to alter the Range("D33") = Range ("D33") + 1 to something more dynamic for when I do need to make another instance of the code with a different range. I tried replacing Range("D33") = Range ("D33") + 1 with Target.Offset(-2,-28) = Target.Offset(-2,-28) + 1 and I end up getting an error which I will try to attach to this comment.

Range("D33") = Range ("D33") + 1

View attachment 39489

Sorry, the code didn't copy correctly. Here is the current VBA code

Sub Turnon()
Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5:G11")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -4) = Target.Offset(0, -4) + Target
Target.ClearContents
Target.Offset(-2, -28) = Target.Offset(-2, -28) + 1
Application.EnableEvents = True

End Sub

 

[offthelip]

The reason is because you only get into the code when you change one of the cells in the range F5to G11. This is in columns 6 and 7 . However you are trying to write into a cell which is 28 columns to the left of column F or G with this line of code:

Target.Offset(-2, -28) = Target.Offset(-2, -28) + 1

Obviously that is impossible. You must remember the excel has unfortunately reversed the order of defining rows and column depending on whether you use letter of numbers.
So F5 is column 6 row 5, e.g.:
Range("F5")
to refer to that in numbers:
it is cells(5,6) i.e. row number then column number, this is also true when you use the offset ,
I don't know where you are trying to write because you can't write 28 rows above row 5 either so,

 

[B Rossio]

Oh, I was trying to write to D33:E39 respectively from F5:G11. I fixed it to where it is Target.Offset(28, -2) and now it works! The last thing I would need help on I thing is having this same code work on different ranges. I tried to copy past it and change the range but nothing happened. Is that because once it steps through F5:G11 it exits the sub?

Sub Turnon()
Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5:G11")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -4) = Target.Offset(0, -4) + Target
Target.ClearContents
Target.Offset(28, -2) = Target.Offset(28, -2) + 1
Application.EnableEvents = True

If Intersect(Target, Range("F19:G25")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, -4) = Target.Offset(0, -4) + Target
Target.ClearContents
Target.Offset(28, -2) = Target.Offset(28, -2) + 1
Application.EnableEvents = True

End Sub

 

[offthelip]

no! the reason is because of this line:

If Intersect(Target, Range("F5:G11")) Is Nothing Then Exit Sub

what this is saying if the cell that has changed is NOT in the range F5 to G11 then exit the subroutine, this means it never gets down to this line:

If Intersect(Target, Range("F19:G25")) Is Nothing Then Exit Sub

becuse if you change cell F19 it exits on the first test.
You can fix the logic by Not exiting the subroutine but just not executing the subsequent bit of code. so try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not (Intersect(Target, Range("F5:G11")) Is Nothing) Then
    If Target.Count = 1 Then
        Application.EnableEvents = False
        Target.Offset(0, -4) = Target.Offset(0, -4) + Target
        Target.ClearContents
        Target.Offset(28, -2) = Target.Offset(28, -2) + 1
        Application.EnableEvents = True
    End If
End If

If Not (Intersect(Target, Range("F19:G25")) Is Nothing) Then
    If Target.Count = 1 Then
        Application.EnableEvents = False
        Target.Offset(0, -4) = Target.Offset(0, -4) + Target
        Target.ClearContents
        Target.Offset(28, -2) = Target.Offset(28, -2) + 1
        Application.EnableEvents = True
    End If
End If


End Sub

 

[B Rossio]

Oh, I was thinking it was saying that if a cell within the range F5:G11 has not been changed, exit. That makes much more sense now, I guess I have to get used to the VBA logic. So with the code you wrote above, you simply step through each specified range and see if something has changed and if it has, you trigger other actions and then move onto the next range. It seems so easy when I look at how you wrote it but it definitely wasn't easy for me. This has been an absolutely huge help and I learned a lot. Thank you so much!