excel - I'm trying to add a count to the number of searches found in my code

Question

My code follows:

Dim ws As Worksheet
Dim ExitLoop As Boolean
Dim SearchString As String, FoundAt As String


Set ws = Worksheets("detail_report")


On Error GoTo Err


Set oRange = ws.Cells

SearchString = "front input"

Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    Set bCell = aCell
    FoundAt = aCell.Address
    Do While ExitLoop = False
        Set aCell = oRange.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            FoundAt = FoundAt & ", " & aCell.Address
        Else
            ExitLoop = True
        End If
    Loop
Else
    MsgBox SearchString & " not Found"
End If

MsgBox "The Search String has been found these locations: " & FoundAt
Exit Sub
Err:
MsgBox Err.Description


End Sub

I added the below code but don't know how to add the counter part:

Dim S As String
Dim count As Integer

Answer 1

With the current code you have you don't even need to use a counter. Instead you can load FoundAt into an array and then use the Ubound to get the total count. Note you have to add 1 since the array is 0 based.

Add these lines before your final Msgbox

Dim iCount() as String
iCount = Split(FoundAt,", ")

MsgBox "The Search String has been found " & UBound(iCount)+1 & " times at these locations: " & FoundAt