Count number of strings found

[vbaNewby]

Hello,

I currently use the find function to find a string within a spreadsheet.

1) I would like to set an integer value and increment everytime I find a string within a worksheet.

2) I would like to use a specific range to start my next find after I know the count.

I found a FindAll function online but I'm wondering if there is anything built in that I can use?

Thanks in advance.

 

[Scott Huish]

If that's the case, why not look backward through the range?

Sub test()
Dim c As Range
Set c = Range("A1:A10").Find("Hello", Range("A1"), , , , xlPrevious)
If Not c Is Nothing Then MsgBox c.Address
End Sub

 

[vbaNewby]

Here is a generalized way to loop through all the instances of a search term in a range. Note that this does not require the use of countOfFinds (COUNTIF) , unless there is some other reason you require that information.

Sub test()
    Dim foundCell As Range
    Dim firstFoundAddress As String
    Dim countOfFinds As Long
    
    Dim searchTerm As String
    searchTerm = "hello"
    
    countOfFinds = 0
    With ThisWorkbook.Sheets("Sheet1").Range("A1:A10")
        Set foundCell = .Find(What:=searchTerm, after:=.Cells(.Rows.Count, 1), _
            LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
            
        If foundCell Is Nothing Then
            MsgBox "No " & searchTerm & " in " & .Address
            Exit Sub
        End If
        
        firstFoundAddress = foundCell.Address
        Do
            countOfFinds = countOfFinds + 1
            MsgBox searchTerm & " found in " & foundCell.Address
            Set foundCell = .FindNext(after:=foundCell)
        Loop Until firstFoundAddress = foundCell.Address
    End With
    
    MsgBox "A total of " & countOfFinds & " '" & searchTerm & "'s were found."
    
End Sub

If you want all of the cells containing "hello" to be extracted, you could either add to a collection or use Application.Union to create a discontinous range, where the line
MsgBox searchTerm & " found in " & foundCell.Address
is currently found.

Thanks Mike, I can certainly work with these to come up with something. Appreciate the help.

 

[vbaNewby]

If that's the case, why not look backward through the range?

Sub test()
Dim c As Range
Set c = Range("A1:A10").Find("Hello", Range("A1"), , , , xlPrevious)
If Not c Is Nothing Then MsgBox c.Address
End Sub

This is also something great to know, I'm going to work on this and let you guys know. Thanks again HOTPEPPER.