If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Count entry only once across multiple worksheets?
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 |
#2
|
|||
|
|||
Count entry only once across multiple worksheets?
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/200...e-elses-macro/ Hope this helps, Hutch "tgcali" wrote: 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 |
#3
|
|||
|
|||
Count entry only once across multiple worksheets?
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" wrote: 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 |
#4
|
|||
|
|||
Count entry only once across multiple worksheets?
Thank you so much! This has been making me pull my hair out. I will
definitely check out that site. "Tom Hutchins" wrote: 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/200...e-elses-macro/ Hope this helps, Hutch "tgcali" wrote: 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 |
#5
|
|||
|
|||
Count entry only once across multiple worksheets?
I really appreciate the response. It looks like I still have a lot to learn!
Thank you!! "JLatham" wrote: 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" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|