How to use VBA Code Find a match and jump to that location

How to use VBA Code to find a match and jump to that location

Requirement:

Find a match and jump to that location.

The user has a list of products in Sheet1 table A1:E5.
The user has the same list of products on Sheet2 all in Column A (A1:A25).
Need an OnClick macro for Sheet1 that finds the matching cell contents in sheet2 and jumps to that location, but one cell to the right.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/150165-find-a-match-and-jump-to-that-location

Solution:

Paste this macro into the Sheet1 Level Module.

Whatever you double-click on in Sheet1 will be located on Sheet2 and the cell immediately to the right will be selected.

Code:

Option Explicit


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim SheetsToSearch, ws As Excel.Worksheet, r As Range
    SheetsToSearch = Array("Sheet2") '// Enter the exact sheet names of sheets to be searched
     
    For Each ws In ThisWorkbook.Sheets
        If Not IsError(Application.Match(ws.Name, SheetsToSearch, 0)) Then
            With ws.Range("A1:AB5000")  '<-------------------------------------------------------------------------------------------------------------------- adjust range on Sheet2 here
                Set r = .Find(Target, [a1], xlValues, xlWhole, xlByRows, , True) 'find the cell whose value is equal to x and activate it
                If Not r Is Nothing Then
                    ws.Activate: r.Activate
                    r.Offset(0, 1).Select
                    Exit Sub
                End If
            End With
        End If
    Next
     
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by Logit.