Search contents of multiline cells within entire workbook

[john3j]

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]

-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]

Use

MsgBox count

[p45cal]

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]

That worked great! I guess I wasn't too far off. I appreciate your help!

[john3j]

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!testbook.xlsx

[Tom Jones]

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]

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]

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