+ Reply to Thread
Results 1 to 5 of 5

VBA to Search for Multiple Values in a column on a specific sheet

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    VBA to Search for Multiple Values in a column on a specific sheet

    I have a sheet with a list of items. I want to create VBA to bring up a search box that asks them what to search for and then either list all of the items and their cell reference OR (preferably), highlight the items in the list.

    I found the following code on another site, but it only lists the first instance that it finds AND I don't like the way it lists it in the same search box. I would prefer that it highlight within the list, jump to the location, or at a minimum, show a message box with the location of the items.

    Sub FindSomething()
    
    Dim Prompt As String
    Dim RetValue As String
    Dim Rng As Range
    Dim RowCrnt As Long
    
    With Sheets("Sheet1")
      Do While True
    
        RetValue = InputBox(Prompt & "Enter item to find:")
        'RetValue will be empty if you click cancel
        If RetValue = "" Then
          Exit Do
        End If
    
          Set Rng = .Columns("C:C").Find(What:=RetValue, After:=.Range("C8"), _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
          If Rng Is Nothing Then
            Prompt = "I could not find """ & RetValue & """"
          Else
            RowCrnt = Rng.Row
            Prompt = "I found """ & RetValue & """ on row " & RowCrnt
          End If
          Prompt = Prompt & vbLf
        Loop
    
      End With
    
    End Sub
    Any help is appreciated.

  2. #2
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: VBA to Search for Multiple Values in a column on a specific sheet

    I don’t quite understand this part:
    AND I don't like the way it lists it in the same search box. I would prefer that it highlight within the list, jump to the location, or at a minimum, show a message box with the location of the items.
    But see if this works as you want:
    This will highlight the found items with yellow.


    Sub FindSomething()
    
    Dim Prompt As String
    Dim RetValue As String
    Dim Rng As Range
    Dim n As Long
    Dim c As Range
    Dim FirstAddress As String
    
        RetValue = InputBox(Prompt & "Enter item to find:")
        'RetValue will be empty if you click cancel
        If RetValue = "" Then Exit Sub
        
    
    With Sheets("Sheet1")
    n = .Range("C" & .Rows.count).End(xlUp).Row
          Set c = .Range("C8:C" & n).Find(What:=RetValue, After:=.Range("C8"), _
                    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    
        If Not c Is Nothing Then
            FirstAddress = c.Address
            Do
                c.Interior.Color = vbYellow
                Set c = .Range("C8:C" & n).FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
        Else
        MsgBox "I could not find """ & RetValue & """"
        End If
    End With
    End Sub

  3. #3
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: VBA to Search for Multiple Values in a column on a specific sheet

    This works perfectly! Thank you!

    One thing I didn't consider is that I don't want the cells to stay yellow indefinitely. I decided to add a button with a macro to clear the highlighting.

    Would there be a line I could add to the code you supplied, to remove any highlighting that is already there if the user runs the code again without pushing the button I created to remove it?
    Last edited by LKERN; 07-30-2019 at 11:17 PM.

  4. #4
    Valued Forum Contributor
    Join Date
    02-02-2016
    Location
    Indonesia
    MS-Off Ver
    Office 365
    Posts
    1,028

    Re: VBA to Search for Multiple Values in a column on a specific sheet

    Just add the blue line:
    With Sheets("Sheet1")
    n = .Range("C" & .Rows.count).End(xlUp).Row
    .Range("C8:C" & n).Interior.Color = xlNone

  5. #5
    Forum Contributor
    Join Date
    11-08-2017
    Location
    Murfreesboro, TN
    MS-Off Ver
    MS 365
    Posts
    168

    Re: VBA to Search for Multiple Values in a column on a specific sheet

    Perfect. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 03-09-2018, 03:25 AM
  2. Replies: 4
    Last Post: 10-01-2015, 11:01 AM
  3. Replies: 3
    Last Post: 03-31-2014, 01:00 PM
  4. [SOLVED] Need to search for a specific string in Column A and delete values within the same row
    By hgeo24 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-27-2014, 04:56 PM
  5. [SOLVED] Search values and copy to specific sheet & cells
    By keis386 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-19-2013, 12:24 PM
  6. Search a Column for a specific Value and Place in Another sheet
    By basketball2524 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2013, 10:19 PM
  7. Search specific column and then copy to new sheet
    By PowerZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-25-2010, 10:04 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1