Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count and Sum multiple values for a single entry | Excel Discussion (Misc queries) | |||
Fprced data entry on multiple worksheets | Excel Worksheet Functions | |||
Populating Multiple Worksheets with one entry | Excel Worksheet Functions | |||
Merging multiple worksheets into one, with ongoing data entry | Excel Discussion (Misc queries) | |||
data entry on multiple worksheets | Excel Discussion (Misc queries) |