Consulting

Results 1 to 9 of 9

Thread: Search contents of multiline cells within entire workbook

  1. #1
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location

    Search contents of multiline cells within entire workbook

    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!

  2. #2
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location
    -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!

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,438
    Location
    Use

    MsgBox count
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #5
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location
    That worked great! I guess I wasn't too far off. I appreciate your help!

  6. #6
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location
    Quote Originally Posted by p45cal View Post
    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

  7. #7
    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

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by john3j View Post
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •