Excel VBA - search box for any value but to stay in row/ column

[Twiino]

Hello Mr. Excel Community

Since 5 hours I am struggling with the following code, which I'd love to expand.
I simply want to mimic the built in ctrl + F search tool via an Input Box, adding the feature to stay in row/column (ActiveCell) while looking for the name (letters not numbers).

Sub Jarvis()

Dim sResult As String

  On Error Resume Next
  
  sResult = InputBox("")
  
  If IsNumeric(sResult) Then 'Select row
    Cells(sResult, ActiveCell.Column).Select
  Else 'Select column - Here I want to add a way to search for names/values and to jump to them, while staying in the current column - Unfortunately didn't find the suiting command
    Cells(ActiveCell.Row, sResult).Select
  
  End If

End Sub••••ˇˇˇˇ

Could anyone of the pros help me out?

Cheers
Matteo

 

[mikerickson]

I think this will do what you want.
Alternalty, you could select the whole column and use Excel's Find. Probably be the same number of mouse clicks.

 

[Twiino]

@mikeerickson: Thank you for your help.

Here is my solution:

Sub Jarvis()

    Dim word, cellRange, firstAddress, c, FindNext, BottomRight As String

    word = InputBox(„insert search term“, "")

    If word = "" Then Exit Sub

    cellRange = "aw2:ku5"
    'change search range

    With ActiveSheet.Range(cellRange)
        Set c = .Find(word)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Range(c.Address, ActiveCell).Select
                    With Selection
                        BottomRight = .Cells(.Rows.Count, .Columns.Count).Select
                    End With
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With
End Sub

If you have a more efficient way, please correct my code.

Cheers
Matteo