How to count PASS/FAIL cells in a range...

Hi. I have a test log with PASS/FAIL. These results are logged in Excel under the column heading PASS/FAIL.
How do I count the number of PASS or FAIL using VBA macro?

So far I have tried:

Sub PASS_FAIL_COUNT()

    Dim count As Integer
    Dim name As String
    count = 0
    Do
        count = count + 1
        name = Range("PASS/FAIL").Select                    
    Loop Until (name <> "PASS" Or name <> "FAIL")
End Sub

This one did not work either:

Sub PASS_FAIL_COUNT()

    Dim r As Range
    Dim cell_count As Integer
    Dim name As String
    cell_count = 0
    Do
        Set r = Range("PASS/FAIL").Select
        name = r.Cells(1, r.Columns.count).Select
        If (name = "PASS" Or name = "FAIL") Then
            cell_count = cell_count + 1
        End If
    Loop Until (name <> "PASS" Or name <> "FAIL")
End Sub

the error is at the name = Range("PASS/FAIL").Select line.

My thought process: once I had selected a range, I can now freely "look" at each string in a cell in that range. Not correct I guess.

Please advice. thank you.

p.s. all this is currently done in the active sheet.

Hi, dtvonly,

you may use CountIf to check the number for each item, CountA if only values are in that range, get the last cell via

MsgBox Range("A" & rows.count).End(xlUp).Row - 1

if you have the header in row 1.

The command you tried to use assumes that there would be a range being named like that which my Excel doesn´t allow.

Ciao,
Holger

Error at line Range("PASS/FAIL").Select line is because the PASS/FAIL is not valid Named Range. I assume that PASS/FAIL is a named range in your code which you are referring to. If this is the case change this to a valid Named Range like Pass_Fail and then you can try the following code to get the count......

Sub PASS_FAIL_COUNT()
Dim rng, cell As Range
Dim Pass, Fail As Integer
Set rng = Range("Pass_Fail")
Pass = WorksheetFunction.CountIf(rng, "pass")
Fail = WorksheetFunction.CountIf(rng, "fail")
MsgBox "Pass count is " & Pass
MsgBox "Fail count is " & Fail
End Sub

or like this.......

Assuming that column C contains the Pass or Fail. If not, change the column C in red color to the column of your choice in the code.

Sub PASS_FAIL_COUNT_()
Dim rng, cell As Range
Dim lr, countP, countF As Long
lr = Cells(Rows.count, "C").End(xlUp).Row
Set rng = Range("C2:C" & lr)
For Each cell In rng
    If cell = "Pass" Then
        countP = countP + 1
    Else
        countF = countF + 1
    End If
Next cell
MsgBox "Pass count is " & countP
MsgBox "Fail count is " & countF
End Sub

2 ways...if you want to use a formula
put a value in col C for pass, and col D for FAIL, using =IF(B2="PASS",1,"")
then sum the columns
or
else VBA code:

range("a1").select
While ActiveCell.Value <> ""
   
select case ActiveCell.value 
    case  "PASS" 
      iPass = iPass + 1
   
    case  "FAIL" 
      iFail = iFail + 1
end select
   
   ActiveCell.Offset(1, 0).Select  'next row
Wend

msgbox ipass & " passed"
msgbox iFail & " failed"