Code line not calculating correctly

[Jim May]

strSearchString = InputBox(Prompt:= _
"Enter a title or other value to search for.", _
Title:="Search Workbook") ' Say I enter zoo

For Each ws In Worksheets
countTot = countTot + Application.CountIf(ws.UsedRange, "=" &
strSearchString) 'problem line see below
Next ws
---------------------------------------------------

On Sheet1 c1 = zoo d4 = go to zoo e10 = zoo << above code
only counts 2 on Sheet1
On sheet 2 g4 = Where is zoo h10 = zoo << above
code only counts 1 on Sheet2
On Sheet3 b3 = zoo h4 = zoo <<
above code only counts 2 on Sheet3

How must I modify countif() above to include zoo when it is within or a part
of cell, but not exclusively?

Using Edit, Find does OK, it finds 3 on Sheet1; 2 on Sheet2 and 2 on Sheet3
Confused...
TIA,

 

[Tom Ogilvy]

=countif(Range,"*zoo*")

would do what you want, so you need to modify your code to produce that

For Each ws In Worksheets
countTot = countTot + Application.CountIf(ws.UsedRange, "*" & _
strSearchString & "*") 'problem line see below
Next ws

 

[Jim May]

Thanks Tom; I got it going...
with:

For Each ws In Worksheets
countTot = countTot + Application.CountIf(ws.UsedRange, _
"=" & "*" & strSearchString & "*")
Next ws