Find string across all worksheets and return count

[sal11235]

Hello chaps, I'm getting stuck amending some code that was kindly supplied to me yesterday from here.

I am attempting to count cells based on a criteria. Countif was used for this originally but now as the criteria has become case sensitive I'm looking to use the Find function.

All I want to do is find all instances of a string (case sensitive), in the workbook and return the count of such instances. I'm getting muddled up with how to initialise and add to the counter. Really grateful for any help :=)

here is the code I have so far..

Function FindString(A02)
For Each sht In ActiveWorkbook.Sheets
If Not sht.Name = "LIST" Then

foundcells = Cells.Find(What:=A02, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

For Each Foundcell In foundcells

xcount = xcount + 1

Next
End If
Next
FindString = xcount
End Function

 

[Weaver]

You have to repeat the find in each worksheet until you've found them all, done here by checking if the current found cell is the same as the first one. Also your original code would only reference the currently active sheet.

Give this a go.

Function countString(ByVal str As String)
    Dim foundCells As Range, xCount As Long, adr As String
    For Each sht In ActiveWorkbook.Worksheets
        With sht
            If Not .Name = "LIST" Then
                Set foundCells = .Cells.Find(What:=str, After:=.Range("A1"), LookIn:=xlValues, LookAt:= _
                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)
                If Not foundCells Is Nothing Then
                    adr = foundCells.Address
                    Do
                        xCount = xCount + 1
                        Set foundCells = .Cells.FindNext(foundCells)
                    Loop While foundCells.Address <> adr
                End If
            End If
        End With
    Next sht
    countString = xCount
    Set foundCells = ActiveSheet.Cells.Find(what:="")
End Function

 

[sal11235]

thank you so much ))

hmm, I get a #VALUE error when I use =countString(A2).

A2 on the "LIST" sheet contains the word "cat".

Any ideas what I'm doing wrong?

get the same error when I fill down A3 (which says "123").. A4 gives me "0", which is the correct answer as there are are no instances of 123 in the workbook

It seems that when there are no instances, it gives the right answer as zero, but when there are instances in the workbook, it comes up with #value

 

[Weaver]

Are you trying to use this as a UDF (i.e in a spreadsheet)? I don't tihnk it will work this way. This might be due to the use of the .findnext function (as this doesn't get used if there are no hits), but I can't be sure.

Works fine if you call it from other VB code tho.

If this is going to be a problem, you may need to rethink your approach.

 

[Weaver]

Thought:

Rather than using UDF, try running the function from code, like this:

Sub fillinfinds()
    With Sheets("list")
        For i = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            .Cells(i, 2).Value = countString(.Cells(i, 1).Value)
        Next
    End With
End Sub

 

[sal11235]

yes I was indeed trying to call it as a UDF, many thanks for that clarification and the quick response :=)

 

[sal11235]

that's the code I already had! Thanks for the effort in digging that out and providing a comprehensive solution ))