Count entry only once across multiple worksheets?

[tgcali]

Hello,

How can I count each worksheet a criteria appears on in a range of cells?
Not the number of times it appears total, but the number of sheets it appears
on.

For example:

The range is H9:H32, the entry I'm needing to count is "other" and there are
250 sheets. I don't want the total number of times the word "other" appears,
but the total number of sheets it appears on in that range. Make sense?

Thank you very much in advance. Any assistance would be greatly appreciated.

tgcali

 

[Tom Hutchins]

Here is one way...

Sub AAAAA()
MsgBox CountShtFind("other", "H9:H32")
End Sub

Public Function CountShtFind(MyStr As String, MyRng As String) As Long
Dim ws As Worksheet, ans
CountShtFind = 0
For Each ws In ActiveWorkbook.Sheets
'search MyRng for MyStr
Set ans = ws.Range(MyRng).Find(What:=MyStr, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
'returns Nothing if not found
If Not ans Is Nothing Then
CountShtFind = CountShtFind + 1
End If
Next ws
End Function

Paste the code in a VBA module in your workbook. Run it from your worksheet
by selecting Tools >> Macro >> Macros >> AAAAA >> Run.

If you are new to macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch

 

[JLatham]

Pretty much the same as Tom Hutchins has offere, only a little different.
And since I was writing while he was posting, figured I'd offer it up anyhow.
Instructions for putting it into your workbook are same as he provided.
Press [Alt]+[F11] to open the VB Editor, choose Insert --> Module and copy
and paste the code into the module presented.

Sub CountFoundOnSheets()
Dim anySheet As Worksheet
Dim searchRange As Range
Dim searchResult As Range
Dim sheetCount As Integer
Dim findWhat As String

findWhat = InputBox$("Enter search for phrase:", "Find?", "")
If findWhat = "" Then
Exit Sub ' no entry made
End If
For Each anySheet In ThisWorkbook.Worksheets
Set searchRange = anySheet.Range("H9:H32")
On Error Resume Next
Set searchResult = searchRange.Find(What:=findWhat, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not searchResult Is Nothing Then
sheetCount = sheetCount + 1
End If
Next
MsgBox findWhat & " appears on " & sheetCount & " sheets."
End Sub

 

[tgcali]

Thank you so much! This has been making me pull my hair out. I will
definitely check out that site.

 

[tgcali]

I really appreciate the response. It looks like I still have a lot to learn!
Thank you!!

Pretty much the same as Tom Hutchins has offere, only a little different.
And since I was writing while he was posting, figured I'd offer it up anyhow.
Instructions for putting it into your workbook are same as he provided.
Press [Alt]+[F11] to open the VB Editor, choose Insert --> Module and copy
and paste the code into the module presented.

Sub CountFoundOnSheets()
Dim anySheet As Worksheet
Dim searchRange As Range
Dim searchResult As Range
Dim sheetCount As Integer
Dim findWhat As String

findWhat = InputBox$("Enter search for phrase:", "Find?", "")
If findWhat = "" Then
Exit Sub ' no entry made
End If
For Each anySheet In ThisWorkbook.Worksheets
Set searchRange = anySheet.Range("H9:H32")
On Error Resume Next
Set searchResult = searchRange.Find(What:=findWhat, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not searchResult Is Nothing Then
sheetCount = sheetCount + 1
End If
Next
MsgBox findWhat & " appears on " & sheetCount & " sheets."
End Sub

Hello,

How can I count each worksheet a criteria appears on in a range of cells?
Not the number of times it appears total, but the number of sheets it appears
on.

For example:

The range is H9:H32, the entry I'm needing to count is "other" and there are
250 sheets. I don't want the total number of times the word "other" appears,
but the total number of sheets it appears on in that range. Make sense?

Thank you very much in advance. Any assistance would be greatly appreciated.

tgcali