PDA

View Full Version : [SOLVED] Search contents of multiline cells within entire workbook



john3j
04-28-2015, 11:39 AM
I have a workbook that generates worksheets based off of xml reports that are selected. Within each worksheet, cell A3 holds multiline data that I would like to search through, but for the whole workbook. There is not a predefined list of worksheets because they are created based off of the xml files. I would like to be able to search for specific strings for cell A3, throughout the entire workbook. I have seen ways of doing this, but all that I have found depend on already knowing what the sheet names are. I would just like to be able to count the instances of the case that I am looking for. I would appreciate the kindness!

john3j
05-01-2015, 04:41 AM
-Update-

I have the following code, but am getting a compile error. Can anyone please help me?

Sub CountSEP()
Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
count = 0
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = ActiveWorkbook.Worksheets.count
' Begin the loop.
For I = 1 To WS_Count
If UCase(Cells("A5")) = "Symantec Endpoint Protection : 12.1.5337.5000" Then
count = count + 1
End If
Next I
MsgBox = count.Value
End Sub


So basically, cell A5 in each worksheet has multiline text. I am trying to loop thru to count how many instances of "Symantec Endpoint Protection : 12.1.5337.5000" are contained in cell A5 of each sheet in the workbook and output the count to a message box. I would really appreciate if anyone could help me figure out what I am doing wrong. I am using Microsoft Excel 2010. Thanks!

Bob Phillips
05-01-2015, 11:04 AM
Use


MsgBox count

p45cal
05-01-2015, 02:56 PM
try:
Sub CountSEP()
Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
count = 0
WS_Count = ActiveWorkbook.Worksheets.count
For I = 1 To WS_Count
If InStr(ActiveWorkbook.Worksheets(I).Range("A5").Value, "Symantec Endpoint Protection : 12.1.5337.5000") > 0 Then count = count + 1
Next I
MsgBox count
End Sub

john3j
05-04-2015, 05:06 AM
That worked great! I guess I wasn't too far off. I appreciate your help!

john3j
05-04-2015, 06:36 PM
try:
Sub CountSEP()
Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
count = 0
WS_Count = ActiveWorkbook.Worksheets.count
For I = 1 To WS_Count
If InStr(ActiveWorkbook.Worksheets(I).Range("A5").Value, "Symantec Endpoint Protection : 12.1.5337.5000") > 0 Then count = count + 1
Next I
MsgBox count
End Sub


p45cal,
I am wondering how I can also show which sheets the value was found in. For instance, if I have 3 sheets (Computer1, Computer2, and Computer3) that all have "Symantec 12" listed in "A1", I would like to see output like "Computer1; Computer2; Computer3" in cell "B2" of the "Dashboard" sheet. My current code gives me "Dashboard; Dashboard; Dashboard; Dashboard; Dashboard" as output.

Here is my code:


Sub CountSEP()
Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
Dim shtName As String
count = 0
shtName = ActiveSheet.Name
WS_Count = ActiveWorkbook.Worksheets.count
For I = 1 To WS_Count
If InStr(ActiveWorkbook.Worksheets(I).Range("A1").Value, "Symantec 12") > 0 Then count = count + 1
shtName = shtName & "; " & ActiveSheet.Name

Next I
Sheets("Dashboard").Range("A2").Value = count
Sheets("Dashboard").Range("B2").Value = shtName
End Sub

I am using Microsoft Office 2010. I have attached the workbook I am testing from. I appreciate all of your help!13297

Tom Jones
05-05-2015, 12:04 AM
Hi, try this:


Sub CountSEP()

Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
Dim shtName As String
count = 0
shtName = ActiveSheet.Name
WS_Count = ActiveWorkbook.Worksheets.count
For I = 2 To WS_Count
If InStr(ActiveWorkbook.Worksheets(I).Range("A1").Value, "Symantec 12") > 0 Then count = count + 1
Sheets("Dashboard").Range("B" & I).Value = Worksheets(I).Name
Next I
Sheets("Dashboard").Range("A2").Value = count
End Sub

p45cal
05-05-2015, 03:00 AM
Here is my code:
Remove:
shtName = ActiveSheet.Name

Change the remaining instance of
ActiveSheet.Name
to:
ActiveWorkbook.Worksheets(I).Name

OK, a little tidying up required like removing the leading semicolon which I'll leave to you.

john3j
05-08-2015, 04:37 AM
Thanks p45cal,

I was able to use the following code and it works like a charm! I appreciate your help.


Sub CountSymantec()
Dim WS_Count As Integer
Dim I As Integer
Dim count As Integer
Dim shtName As String
count = 0
WS_Count = ActiveWorkbook.Worksheets.count
For I = 1 To WS_Count
If InStr(ActiveWorkbook.Worksheets(I).Range("B5").Value, "Symantec Endpoint Protection : 12") > 0 Then
count = count + 1
Sheets("Dashboard").Range("E33").Value = Sheets("Dashboard").Range("E33").Value & ActiveWorkbook.Worksheets(I).Name & "; "
End If
Next I
Sheets("Dashboard").Range("D33").Value = count
End Sub