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]

If you post your existing code we may be able to help spot the problem.
When posting code please use the correct formatting tool which you get by clicking the cloud icon with "VBA" underneath it.

 

[B Rossio]

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Target.Offset(0, -4) = Target.Offset(0, -4) + Target
Target.ClearContents

End Sub

 

[offthelip]

Your major problem is the fact that your code is triggered by the worksheet change event, however the code itself makes changes to the worksheet so it triggers itself in a loop.
To avoid this you need to turn of the "events" trigger when you write to the worksheet by addding these lines. obviously after turning the events off you need to turn them back on agian after you have written to the workhseets, so try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False ' this is vital!!
Target.Offset(0, -4) = Target.Offset(0, -4) + Target
Target.ClearContents
Range("D33")=Range("D33")+1   ' this is to add 1 to d33
Application.EnableEvents = True ' and this to trun them back on again

End Sub

 

[B Rossio]

THAT WORKED!! I've been struggling with this for a couple weeks now (I'm new to VBA). Thank you! The only thing is I need this same exact function to work on multiple different cells such as G5 - G11 and F5 - F11. Once I tried to use the same code and alter the Range, it won't work on anything other than F5.

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5")) 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
Range("D33") = Range("D33") + 1
Application.EnableEvents = True

'Second instance of some code execution
If Intersect(Target, Range("G5")) 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
Range("E33") = Range("E33") + 1
Application.EnableEvents = True

End Sub

 

[B Rossio]

Weird, it worked but when I went back into the code, I tried adding another instance to see if that would work. It didn't so I deleted the new section of code, went back into the worksheet and it doesn't work anymore. I even tried erasing it all and copying and pasting in your code again and it still doesn't work. So I tried saving it with your code, closing and opening again and still nothing.

 

[offthelip]

What has happened is you have probably managed to turn the application events OFF and not turned them back on again. when this occurs nothing happens, no recalculations, nothing gets triggered.
I suggest you copy this code into an new separate subroutine and run it. it is always usefule to have this around:

sub Turnon
Application.EnableEvents = True

end sub

to get it to work over the range F5 to G11 try this:

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
Application.EnableEvents = True
Range("D33") = Range("D33") + 1
End Sub

 

[B Rossio]

Ok, so F5 works again with the Sub Turnon. So that's great, but how can I have G5:G11 do the same thing as F5?

Sub Turnon()
Application.EnableEvents = True

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("F5")) 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
Range("D33") = Range("D33") + 1
Application.EnableEvents = True

If Intersect(Target, Range("G5")) 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
Range("E33") = Range("E33") + 1
Application.EnableEvents = True

End Sub

 

[offthelip]

See my second bit of code in post 7, there is a very small modification :

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

 

[B Rossio]

Wow, I completely missed that sorry. That works great. In the future I'll be adding different charts that will require this same function. However, the cells will not be concurrent to F5:G11 but rather F19:G25 for example. Would I simply copy that snippet of code and alter the range or would something different need to happen?