Jump to cell in selected column and row containing value in another column

Question

I am trying to write a macro to jump to a cell in Excel, where I would select a specific column by highlighting it, and then jump to the row containing a searched value from different column. I know how to make the macro and shortcut, it is just the vba code I am struggling with. I found this macro just need to know how to change it so it jumps to the row containing a searched value in a specific column, and not the row number of the input.

Sub JumpTo()
'Description: Select the specified row or column
  'If row is specified, active column is used
  'If column is specified, active row is used
'Source: https://www.excelcampus.com/vba/jump-to-row-column-keyboard-shortcut

Dim sResult As String

  On Error Resume Next 'Blanket error handling
  
  'Display inputbox to prompt user for row/column
  sResult = InputBox("Type a row number or column letter and press Enter.", "Jump To...")
  
  If IsNumeric(sResult) Then 'Select row
    Cells(sResult, ActiveCell.Column).Select
  Else 'Select column
    Cells(ActiveCell.Row, sResult).Select
  End If

End Sub

The code as written returns the Numeric row# and not the row of an inputted value in a specific column. In my case I want to return the row of a value in column "A" — Rw Stearns, Jan 14 '22 at 23:40
@RwStearns Posted an answer enriched by several essential hints to programming I considered helpful. - *Allow me a hint as new contributor: Consider to accept by ticking the green checkmark near the answer if you found it also helpful; this way you might give other users some guidance, too. C.f. ["Someone answers"](https://stackoverflow.com/help/someone-answers)* — T.M., Jan 18 '22 at 18:27

score 0 · Answer 1 · answered Jan 15 '22 at 19:59

Several hints (in addition to comments in code example)

Use Option Explicit to force type declarations and try to be explicit. Example: Dim r As Variant though an implicit Dim r would be sufficient.
Fully qualify your sheet reference(s). Example: Cells alone would refer to any currently active sheet which needn't be the one you have focus to. Prefix e.g. by ws.Cells(r, "A")... or use a point prefix within a With ws - End With structure.
Declare the accurate sheet type specifically. Example: As you are referring to worksheets, don't code Dim ws As Sheet including shapes, too. Narrow the field via Dim ws As Worksheet.
Refer to the wanted sheet either via its tabular sheet name like e.g. Set ws = ThisWorkbook.Worksheets("Sheet1") or the project's sheet Code(Name) as indicated in VB Editor's properties window, e.g. Set ws = Sheet1 as variable name (without quotes).
Avoid On Error Resume Next without specific error handling and exact knowledge about what you are doing. You will find numerous examples at SO.
As a general rule avoid .Select and .Activate whereever possible, if not wanted or needed expressly (like in this example). See How to avoid using select in Excel VBA.

Code example

ad 1+2) Note that InputBox (contrary to Application.InputBox) returns string results only. So this simple example code assumes string values or numeric string inputs which would be changed to doubles to allow findings.
ad 3) Only one way to find matching values (case insensitively) is by Application.Match. Alternatively you could apply a Range.Find as proposed in comment or loop through a 2-dim datafield array one by one etc.
The Application.Match approach (which doesn't pretend to be the best one) has the bonus to allow an easy positive check for valid row results via IsNumeric detecting/ignoring Error 2042 values (to prevent eventual errors when trying to use an error results) ... Alternatively you could check negatively via If Not IsError(r) Then.

Option Explicit                                 ' Module head: Force type declarations
Sub JumpTo()
'0) Fully qualify sheet reference
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")  ' << change to your needs

'1) Display inputbox to prompt user for value input
    Dim srch As Variant                          ' provide for possible change to double   
    srch = InputBox("Type a value to be searched in column A and press Enter.", "Jump To...")
'2) Change numeric search string to double
    If IsNumeric(srch) Then srch = CDbl(srch)    
'3) Get row number of search value and go to found cell
    With ws                                      ' refer to worksheet object ws
        'a) Execute Match                        ' case independant
        Dim r As Variant                         ' needs Variant for IsNumeric check!
        r = Application.Match(srch, .Columns("A"), 0)
        'b) Check if Match returns a valid row number
        If IsNumeric(r) Then                     ' ignores error 2042 if not found
            .Cells(r, "A").Select                ' or: .Cells(r, 1).Select
        Else
            MsgBox "Value " & srch & " not found!", vbExclamation, "Error 2042"
        End If
    End With
End Sub

Jump to cell in selected column and row containing value in another column

1 Answers1