How to write a counting Macro/VBA that looks for a certain Value

[alanmur]

I am hoping someone can help me with this. Every month i run some reports were i have to count the number of entries in a column (F) that meet certain criteria in an excel spreadsheet. Then i enter the counted value into another cell for reporting. I know i can use and have the countif formula but was hoping someone could point me in a direction that i could run a macro on a spreadsheet and have the value put in a certain cell. Also there are 15 different types of entries i do this for. Thanks for any help you can offer.

W1 1-May-13 2442195 956 MEETING
W1 1-May-13 2442195 958 SHIFT START UP
W1 1-May-13 2442195 960 CLEAN UP
W1 1-May-13 2442382 951 NPP - LUNCH 1
W1 2-May-13 2442386 956 MEETING
W1 2-May-13 2443175 951 NPP - LUNCH 1
W1 2-May-13 2443175 13 MECHANICAL BREAKDOWN
W1 2-May-13 2443175 13 MECHANICAL BREAKDOWN
W1 3-May-13 2443774 956 MEETING
W1 3-May-13 2443970 951 NPP - LUNCH 1
W1 6-May-13 2441231 951 NPP - LUNCH 1
W1 6-May-13 2441231 960 CLEAN UP
W1 6-May-13 2441231 960 CLEAN UP
W1 6-May-13 2441231 958 SHIFT START UP
W1 6-May-13 2441231 956 MEETING
W1 7-May-13 2443971 956 MEETING
W1 7-May-13 2443971 958 SHIFT START UP
W1 7-May-13 2438683 951 NPP - LUNCH 1
W1 7-May-13 2438687 10 OTHER PROCESS FAILURE

 

[ranman256]

Can you record a macro, then make a pivot table to do the counts...
Then record another macro that copies the values of the pivot to your reporting sheet?

 

[Chris Mack]

You can implement COUNTIF pretty easily in VBA, e.g.,

Sub COUNTIF()
    For r = 1 To 15
        n = Evaluate("COUNTIF(ReferenceSheet!F:F,""" & Cells(r, 1) & """)")
        Cells(r, 2) = n
    Next
End Sub

You might need to change the code highlighted in red.

Hope this helps,

Chris.