1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Multiple search in one sheet EXCEL

Discussion in 'Business Applications' started by SlickMlle, Dec 21, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. SlickMlle

    SlickMlle Thread Starter

    Joined:
    Dec 21, 2012
    Messages:
    3
    Hi,
    I need to search for multiple names (20-50) in one sheet which contains thousands of words. Is there a macro I can use? It's a very simple sheet and I can do the search in 1 column only if needed.
    Thanks
     
  2. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Yes this can be done. Will the 20-50 names be listed in the same workbook? What do you want to do with the values that are found?

    Rollin
     
  3. SlickMlle

    SlickMlle Thread Starter

    Joined:
    Dec 21, 2012
    Messages:
    3
    Hello Rollin,
    Thanks for the help.
    Yes, all the names are in the same worksheet; I just need to locate them and highlight them.
     
  4. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    I've provided 2 macros below. The first macro will search the entire sheet for matching values while the second macro will search a specific column for matching values. Just change the portions highlighted in red to match your sheet names / data layout.


    USE THE FIRST MACRO BELOW TO SEARCH FOR MATCHING VALUES ON THE ENTIRE SHEET

    Code:
    Sub FindValues()
    
        '''Change to sheet name containing list of values to search
    vListSheet = "[COLOR="Red"]Sheet2[/COLOR]"
    
        '''Change to column letter containing list of values to search
    vListColumn = "[COLOR="Red"]A[/COLOR]"
    
        '''Change to row number of first value to search
    vStartList = [COLOR="Red"]1[/COLOR]
    
        '''Change to sheet name where you would like to search
    vFindSheet = "[COLOR="Red"]Sheet1[/COLOR]"
    
    
    vLastRow = Sheets(vListSheet).Cells(Rows.Count, vListColumn).End(xlUp).Row
    
    
    For Each vSearch In Sheets(vListSheet).Range(vListColumn & vStartList & ":" & vListColumn & vLastRow).Cells
    
    Set vFound = Sheets(vFindSheet).Cells.Find(What:=vSearch, MatchCase:=False)
    
    If Not vFound Is Nothing Then
    vStart = vFound.Address
    
    Do
    Range(vFound.Address).Interior.ColorIndex = 6
    Set vFound = Cells.FindNext(vFound)
    Loop Until vFound.Address = vStart
    End If
    
    Next vSearch
    
    End Sub
    USE THIS MACRO TO SEARCH FOR MATCHING VALUES IN A PARTICULAR COLUMN

    Code:
    Sub FindValues2()
    
        '''Change to sheet name containing list of values to search
    vListSheet = "[COLOR="Red"]Sheet2[/COLOR]"
    
        '''Change to column letter containing list of values to search
    vListColumn = "[COLOR="Red"]A[/COLOR]"
    
        '''Change to row number of first value to search
    vStartList = [COLOR="Red"]1[/COLOR]
    
        '''Change to sheet name where you would like to search
    vFindSheet = "[COLOR="Red"]Sheet1[/COLOR]"
    
       ''''Change to column that you would like to search
    vFindColumn = "[COLOR="Red"]C[/COLOR]"
    
    
    vLastRow = Sheets(vListSheet).Cells(Rows.Count, vListColumn).End(xlUp).Row
    
    
    For Each vSearch In Sheets(vListSheet).Range(vListColumn & vStartList & ":" & vListColumn & vLastRow).Cells
    
    Set vFound = Sheets(vFindSheet).Columns(vFindColumn & ":" & vFindColumn).Cells.Find(What:=vSearch, MatchCase:=False)
    
    If Not vFound Is Nothing Then
    vStart = vFound.Address
    
    Do
    Range(vFound.Address).Interior.ColorIndex = 6
    Set vFound = Cells.FindNext(vFound)
    Loop Until vFound.Address = vStart
    End If
    
    Next vSearch
    
    End Sub
     
  5. SlickMlle

    SlickMlle Thread Starter

    Joined:
    Dec 21, 2012
    Messages:
    3
    Thanks much Rollin!
     
  6. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1081905

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice