VBA code to count certain criteria (1 Viewer)

Lensmeister

Registered User.
Local time
Today, 17:59
Joined
Feb 18, 2009
Messages
65
Tight I'll change that when I get home.

You are a star Brian

I will adapt it just to count the colus to giver an overall stat of all the pens scored.
 

Brianwarnock

Retired
Local time
Today, 17:59
Joined
Jun 2, 2003
Messages
12,701
Oh dear! Oh dear!

Concentrating on solving the immediate problems I lost sight of the bigger picture. If you use a function to do this calculation then everytime that you change anything in the data range the function is executed, and as it scans through a lot of cells it is not the quickest, now that you also want to calculate the total number of penalties , so to function executions.

I would do both calculations in one macro assigned to a command button on the profile sheet and just run the macro when required.

I've amended the code , this places the total penalties in the column next to the individual, but you will probably want to change that.

Brian

Code:
Sub mysub()
Dim rng As Range
Dim pentot As Long, allpen As Long
pentot = 0
allpen = 0
For Each rng In Range("ALLGoals")
    If rng.Interior.ColorIndex = 35 Then
    allpen = allpen + 1
        If rng.Value = Range("filename").Value Then
        pentot = pentot + 1
        End If
    End If
Next rng
Range("Penalties").Offset(0, 1) = allpen
Range("Penalties") = pentot

End Sub
 

Users who are viewing this thread

Top Bottom