modified code move to item as in first row by inputbox without scroll bar

[MKLAQ]

first thanks for @DanteAmor & Dave for update code twice . now based on updating @DanteAmor I would when search and highlight the item should show the item as is in first row without using scroll bar to see the item where is it . if it is 100 rows or in 200 rows .... with considring if there is duplicate item should move to last duplicate item as is in first row .

Sub CommandButton2_Click()
  Dim Search          As Variant
  Dim c               As Range, rng As Range
  Dim sh              As Worksheet
  Dim Response        As VbMsgBoxResult
  Dim msg             As String, FirstAddress As String
  Dim Prompts(1 To 2) As String, Prompt As String
    
  Prompts(1) = "Serial number found On row(s) " & Chr(10) & Chr(10)
  Prompts(2) = "Serial number Not found" & Chr(10) & Chr(10)
  Set sh = ThisWorkbook.Worksheets("list1")
  Set rng = sh.Range("B2:B" & Rows.Count)
 
  Do
    rng.Interior.Color = xlNone
    Do    'display inputbox
      Search = InputBox("Enter Search Number Value:", "Search")
      If StrPtr(Search) = 0 Then Exit Sub 'cancel pressed
    Loop Until Len(Search) > 0
   
    If IsNumeric(Search) Then Search = Val(Search)
    Set c = rng.Find(Search, , xlValues, xlWhole, xlByRows, xlNext, True)
    If Not c Is Nothing Then
      FirstAddress = c.Address
      msg = Prompts(1)
      Do
        c.Interior.Color = vbYellow
        msg = msg & c.Row & Chr(10)
        Set c = rng.FindNext(c)
      Loop While Not c Is Nothing And FirstAddress <> c.Address
    Else
      msg = Prompts(2) & Search & Chr(10)
    End If
          
    Response = MsgBox(msg & Chr(10) & "Do you want To make another search?", 36, "Results")
    msg = ""
  Loop Until Response = vbNo
End Sub

thanks

 

[MKLAQ]

I hope some body help

 

[mumps]

See if this does what you want:

Do
    c.Interior.Color = vbYellow
    msg = msg & c.Row & Chr(10)
    c.Activate
    Application.Goto ActiveCell.EntireRow, True
    Set c = rng.FindNext(c)
Loop While Not c Is Nothing And FirstAddress <> c.Address

 

[MKLAQ]

magnificent ! this is what I look for it.
many thanks for your help

 

[mumps]

You are very welcome.