Showing COUNTIF results in a Message Box

Question

I am currently using this code to display the number of "RCA Pending" found in a column. The message box does show the correct number of times it is found in the column, however, it creates a box for each instance (i.e. if there are 2 instances in the column, when the workbook is open it will display "Found 2 RCA Pending(s)", then when the user clicks OK, a second popup saying the same thing appears. If there are 5, you will get 5 consecutive popups).

Sub Auto_Open()

    Dim row As Range
    For Each row In Worksheets("Swivel").UsedRange.Rows
        If row.Cells(1, "AB").Value = "RCA Pending" Then
            MsgBox "Found " & WorksheetFunction.CountIf(Columns("AB"), "RCA Pending") & " RCA Pending(s)", vbInformation, "RCA Pending Found"
        End If
    Next row

End Sub

How can this be altered to show the total number of instances and not get multiple popups?

As a side note, I am using UsedRange because the range is always growing. The module that this code resides in has Option Explicit at the top.

Answer 1

Is this what you are trying?

Sub Auto_Open()
    Dim instances As Long

    instances = WorksheetFunction.CountIf(Columns("AB"), "RCA Pending")

    If instances <> 0 Then _
    MsgBox "Found " & instances & " RCA Pending(s)", vbInformation, "RCA Pending Found"
End Sub

OR

Sub Auto_Open()
    Dim instances As Long

    instances = WorksheetFunction.CountIf(Columns("AB"), "RCA Pending")

    MsgBox "We Found " & instances & " instances of RCA Pending(s)", _
    vbInformation, "RCA Pending Found"
End Sub