+ Reply to Thread
Results 1 to 8 of 8

search with input box

Hybrid View

ccsmith search with input box 11-17-2012, 12:25 PM
HSV Re: search with input box 11-17-2012, 12:39 PM
ccsmith Re: search with input box 11-17-2012, 12:54 PM
HSV Re: search with input box 11-17-2012, 01:03 PM
ccsmith Re: search with input box 11-17-2012, 01:33 PM
vipul.halbe Re: search with input box 11-17-2012, 01:09 PM
HSV Re: search with input box 11-17-2012, 01:56 PM
ccsmith Re: search with input box 11-18-2012, 05:51 AM
  1. #1
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Budapest
    MS-Off Ver
    Excel 2010
    Posts
    155

    search with input box

    I got a code for search number in a range. I would like to use this one to add a next button (option) to this macro to find the next results as well.
    Could it be possible to highlight the entire row where the result found?


    Sub searchmain()
    Dim InputItem As Variant
    Dim c As Range
    
        InputItem = ""
        InputItem = Application.InputBox("Please enter the number you wish to find", "Number", "")
        If InputItem = "" Or InputItem = False Then Exit Sub
    
    Set c = Columns(7).Find(InputItem, LookAt:=xlWhole, MatchCase:=False)
    
        If c Is Nothing Then
        MsgBox "not found", 48, "error"
        Else
        c.Select
        End If
    
    End Sub
    Last edited by ccsmith; 11-18-2012 at 05:50 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    860

    Re: search with input box

    Would this help?

    Sub searchmain()
    Dim InputItem As Variant
    Dim c As Range
    Dim firstaddress As String
    
        InputItem = ""
        InputItem = Application.InputBox("Please enter the number you wish to find", "Number", "")
        If InputItem = "" Or InputItem = False Then Exit Sub
    
    Set c = Columns(7).Find(InputItem, , xlValues, xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
      Do
         c.EntireRow.Interior.ColorIndex = 6
         Set c = Columns(7).FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
        Else
      MsgBox "not found", 48, "error"
     End If
    End Sub
    Harry.

  3. #3
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Budapest
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: search with input box

    Thanks Herry. I don't think about this solution. I don't need to change the color of the row, because i have a many formatting and conditional formats in the cells.
    I would like to mark (highlight) the row where the result found. Like i click on the row number. But it will be more important to I can find the NEXT result as well without quitting the macro.

  4. #4
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    860

    Re: search with input box

    Something like this.
    Sub searchmain()
    Dim InputItem As Variant, sq
    Dim c As Range
    Dim firstaddress As String
    
        InputItem = ""
        InputItem = Application.InputBox("Please enter the number you wish to find", "Number", "")
        If InputItem = "" Or InputItem = False Then Exit Sub
    
    Set c = Columns(7).Find(InputItem, , xlValues, xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
      Do
         sq = sq & "," & c.Address
         Set c = Columns(7).FindNext(c)
         
      Loop While Not c Is Nothing And c.Address <> firstaddress
       Range(Mid(sq, 2)).EntireRow.Select
        Else
      MsgBox "not found", 48, "error"
     End If
    End Sub

  5. #5
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Budapest
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: search with input box

    It's working well. Could it be somehow get information which row nr. contains the results in case of more than 1 hit?

  6. #6
    Registered User
    Join Date
    06-29-2011
    Location
    MUMBAI
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    85

    Re: search with input box

    Sub searchmain()
    Dim InputItem As Variant
    Dim c As Range
    Dim firstaddress As String
    
        InputItem = ""
        InputItem = Application.InputBox("Please enter the number you wish to find", "Number", "")
        If InputItem = "" Or InputItem = False Then Exit Sub
    
    Set c = Columns(7).Find(InputItem, , xlValues, xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
      Do
        abc = MsgBox(c.Address, vbYesNo)
        If abc = vbYes Then exit sub
        
         Set c = Columns(7).FindNext(c)
      Loop While Not c Is Nothing And c.Address <> firstaddress
        Else
      MsgBox "not found", 48, "error"
     End If
    End Sub
    will this help?
    Last edited by vipul.halbe; 11-17-2012 at 01:12 PM.

  7. #7
    Valued Forum Contributor
    Join Date
    02-12-2011
    Location
    The Netherlands
    MS-Off Ver
    365
    Posts
    860

    Re: search with input box

    Sub searchmain()
    Dim InputItem As Variant, sq
    Dim c As Range
    Dim firstaddress As String
    
        InputItem = ""
        InputItem = Application.InputBox("Please enter the number you wish to find", "Number", "")
        If InputItem = "" Or InputItem = False Then Exit Sub
    
    Set c = Columns(7).Find(InputItem, , xlValues, xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
      Do
         sq = sq & ",row " & c.Row
         Set c = Columns(7).FindNext(c)
    
      Loop While Not c Is Nothing And c.Address <> firstaddress
       MsgBox Join(Split(Mid(sq, 2), ","), vbLf)
        Else
      MsgBox "not found", 48, "error"
     End If
    End Sub

  8. #8
    Forum Contributor
    Join Date
    04-21-2010
    Location
    Budapest
    MS-Off Ver
    Excel 2010
    Posts
    155

    Re: search with input box

    Thanks HSV. I will use your last solution.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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