How to use VBA Code to find a match and jump to that location
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to enter formula only if sheet exists |
| How to put formula in VBA CODE |
| How to create excel VBA code to ascertain if cell value is greater than 2 cell values, show a pop up message |
| How to create VBA code to compare dates |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.