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]

This looks in the entire spreadsheet with no loop:

Sub CountStrings()
Dim x As Long
x = Application.WorksheetFunction.CountIf(Cells, "?*")
MsgBox x
End Sub

 

[vbaNewby]

This looks in the entire spreadsheet with no loop:

Sub CountStrings()
Dim x As Long
x = Application.WorksheetFunction.CountIf(Cells, "?*")
MsgBox x
End Sub

Thanks for the reply HOTPEPPER. This is what I was looking for.

Another question: Is there a way to tell Cells.find to start at a specific location?

What i am trying to do, for example:

Range("A1:A10")

Say I have the string "Hello" in A3 and in A9. I want to use your above CountIf function to recognize two "Hello"

Based on knowing that I have two finds, I want to start my Cells.find after A3 so the first Cells.find call returns the A9 record.

Since I don't know the location of my two "Hello" I simply can't select a cell after my first A3 occurance and then run Cells.find to find A9.

Is there anything out there to help me achieve this?

 

[Scott Huish]

Why are you using Find, why not just use the Countif on that Range to get your count?

Sub CountStrings()
Dim x As Long
x = Application.WorksheetFunction.CountIf(Range("A1:A10"), "Hello")
MsgBox x
End Sub

 

[vbaNewby]

Why are you using Find, why not just use the Countif on that Range to get your count?

Sub CountStrings()
Dim x As Long
x = Application.WorksheetFunction.CountIf(Range("A1:A10"), "Hello")
MsgBox x
End Sub

I need to use both. Countif to get my count, then based on that count I want to use Cells.find

IN other words I need to be able to recognize that a string is present in a sheet twice. That we have worked out.

From there I need to use the regular find (or possibly something else) to tell it to pick up either the first or second occurence of my string.

if count >= 2
    'find second "hello"
else
    'find first "hello"

I'm just not sure how to accomplish this. Make sense?

 

[mikerickson]

When using the .Find method, the After argument can be used to control where in the range Excel starts looking.

But, as Hotpepper says, if counting is the only thing that you are doing, the .Find method is not needed.

 

[vbaNewby]

When using the .Find method, the After argument can be used to control where in the range Excel starts looking.

But, as Hotpepper says, if counting is the only thing that you are doing, the .Find method is not needed.

Hi mikerickson, thanks for the reply.

Yes, this is exactly what I am after. The only thing that is confusing me is getting the address of the cell.

So using my A1:A10 "hello" example. I found 2 instances of "hello" But now how do I use the After argument in the .Find method to tell it to start looking at cells A4 and beyond (upto A10) so the first find call returns my "hello" in A9?

 

[vbaNewby]

Mike and Hotpepper,

A possible solution I was thinking of trying was to use countif to return 2. And then something like this.

Set FoundCell1 = Cells.Find(what:=myCell, After:=ActiveCell, LookIn:=xlFormulas _
            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=True)

If Not FoundCell1 Is Nothing Then
   'found something
                firstAddress = FoundCell.Address
   'now use find again but start it after firstAddress


Set FoundCell2 = Cells.Find(what:=myCell, [B]After:=firstAddress[/B], LookIn:=xlFormulas _
            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=True)

But this solution is rather painful because it is not dynamic. If I find that there are 3 "hello" then I want to look for my third....

 

[mikerickson]

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.

 

[Scott Huish]

You just need the Last instance of the string?