This macro uses the caption written on a command button to search row 2 for a value that is the same as the caption. It works well, but I am trying to make it a little cleaner by using the Application.GoTo Scroll method instead of MyRange.Select

However, I can't seem to get the named range "MyRange" to work in this method. Any recommendations on how to rewrite the code?

Sub Navigator_Sections()

Dim ButtonName As String
Dim lnRow As Long, lnCol As Long
Dim MyRange As Range
ButtonName = ActiveSheet.Buttons(Application.Caller).Caption

    lnRow = 2
    lnCol = ActiveSheet.Cells(lnRow, 1).EntireRow.Find(What:=ButtonName, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).Column
    
    Set MyRange = Cells(lnRow, lnCol)
    Set MyRange = MyRange.Offset(0, -2)
    
    Application.Goto Reference:=ActiveSheet.Range("MyRange"), Scroll:=True


End Sub

CodePudding user response：

Application.Goto MyRange, Scroll:=True

CodePudding user response：

Two issues:

1. Always test if the Find succeeded.
2. MyRange is a Range object already. No quotes, no reference to ActiveSheet.

Set myRange = ActiveSheet.Rows(lnRow).Find( _
                  What:=ButtonName, LookIn:=xlValues, LookAt:=xlPart, _
                  SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
                  MatchCase:=False)

If Not myRange Is Nothing Then
   If myRange.Column > 2 Then
       Application.GoTo MyRange.Offset(,-2), True
   End If
End If