Results 1 to 7 of 7

Thread: Code help to search sheets in Excel

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    Code help to search sheets in Excel

    Hello All,
    I need some help writing some VB code in Excel to search all worksheets in a workbook (excluding 2 of them) for a specific string. When that string is found in any of the worksheets, I want to place a specific value in the cell 8 columns to the right. I can adapt the code as needed, I just need to find out a general set of code to do this. Please help.

    Thanks!

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Code help to search sheets in Excel

    Thread moved to the 'Office Development/VBA' forum... note that while it certainly isn't made clear, the "VB Editor" in Office programs is actually VBA rather than VB, so the 'VB6' forum is not really apt

  3. #3
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Code help to search sheets in Excel

    Code:
    Sub SearchWord()
        Dim strWord As String
        strWord = "The value you are looking for"
        Dim sh As Worksheet
        Dim rng As Range
        Dim c
        
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "ExcludeSheet1" And sh.Name <> "ExcludeSheet2" Then
            Set rng = sh.UsedRange
            Set c = rng.Find(strWord, LookIn:=xlValues)
        If Not c Is Nothing Then
            addr = c.Address
            MsgBox addr
            sh.Range(addr).Offset(0, 8).Value = "The specific value you wish place here "
            Exit For
        End If
        End If
    Next sh
        
    End Sub
    HTH

  4. #4
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Code help to search sheets in Excel

    Does the find string appear only once in any given sheet?

  5. #5
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Code help to search sheets in Excel

    Quote Originally Posted by vbfbryce View Post
    Does the find string appear only once in any given sheet?
    I asked myself myself the same question.
    I can not find my crystal ball

  6. #6

    Thread Starter
    New Member
    Join Date
    Feb 2012
    Posts
    4

    Re: Code help to search sheets in Excel

    Sorry, was away for the weekend. To answer the question, in this case, yes the string will only appear once on each sheet. However, this is code that I will likely use regularly so knowing how the code would look if the string will be found multiple times would be helpful as well. Will the code change if that is the case?

    Thanks for the help!

  7. #7
    Addicted Member 3com's Avatar
    Join Date
    Jul 2013
    Location
    Spain
    Posts
    253

    Re: Code help to search sheets in Excel

    Code:
    Sub SearchWord()
    
        Dim strWord As String
        strWord = "The value you are looking for"
        Dim sh As Worksheet
        Dim rng As Range
        Dim c
        
    For Each sh In ActiveWorkbook.Worksheets
        If sh.Name <> "ExcludeSheet1" And sh.Name <> "ExcludeSheet2" Then
            Set rng = sh.UsedRange
            Set c = rng.Find(strWord, LookIn:=xlValues)
        If Not c Is Nothing Then
            addr = c.Address
            sh.Range(addr).Offset(0, 8).Value = "The specific value you wish place here "
            Do
                addr = c.Address
                Debug.Print sh.Name, addr
                c.Value = strWord
                Set c = rng.FindNext(c)
            Loop While Not c Is Nothing And c.Address <> addr
        End If
    End If
    Next sh
        
    End Sub
    HTH

Posting Permissions

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



Click Here to Expand Forum to Full Width