2

I want to compute number of times a specified string appears in my entire workbook in some column. I have tried this formula =COUNTIF(sheet1:sheetn!C2:C150; "*string*") But it renders the #VALUE! error.

What's wrong with my formula? How can I get this count?

Excellll
  • 12,847
mounaim
  • 297

3 Answers3

2

Here is the equivalent VBA code to count the number of cells containing at least one string

Sub StringCounter()
    Dim r As Range, s As String, i As Long
    s = "*string*"
    For Each sh In Sheets
        Set r = sh.Range("C2:C150")
        i = i + Application.WorksheetFunction.CountIf(r, s)
    Next sh
    MsgBox i
End Sub

EDIT#1:

Here is the code to count the number of strings It will count multiple string within a single cell:

Sub StringCounter_II_The_Sequel()
Dim r As Range, s As String, i As Long
Dim rr As Range, L As Long
s = "string"
L = Len(s)
For Each sh In Sheets
    Set r = sh.Range("C2:C150")
        For Each rr In r
            txt = rr.Text
            If InStr(txt, s) > 0 Then
                i = i + (Len(txt) - Len(Replace(txt, s, ""))) / L
            End If
        Next rr
Next sh
MsgBox i
End Sub

EDIT#2:

The math works as follows:

  1. say there are three string in a cell and the number of characters in that cell is 100
  2. the replace removes all three instances and the new length is 82
  3. the subtract results in 18
  4. the division results in 3 (because the word string is 6 characters long)
Gary's Student
  • 19,636
1

You need to define each range for each sheet e.g.

COUNTIF(Sheet1!C2:C150,"*string*")+COUNTIF(Sheet2!C2:C150,"*string*") ...

If you want to be clever, you can say put each sheet in the column A, and in column B use

=COUNTIF(INDIRECT("Sheet"&A1&"!A1:A5"),"*string*")

Or whatever fits your naming scheme. Then drag it down for each count and sum that.

Raystafarian
  • 21,963
  • 12
  • 64
  • 91
1

You can write a pretty straightforward user-defined function in VBA to do this. Paste the following code into a new module (for instructions on how to do this, see this post):

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    'Limit search area to specified range.
    addr = InRange.Address
    Set rng = s.Range(addr)
    'Keep a running tally of the number of instances of the word in the specified range on each sheet.
    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, "*" & SearchFor & "*")
Next s
WBCountString = wbcs
End Function

Once you have this code in a module, you can use the function from the worksheet. For example, to count the number of instances of potato in column C on all worksheets, you would use the following syntax.

=WBCountString("potato",C:C)
Community
  • 1
Excellll
  • 12,847