Using a reference cell for Find function

Question

I have a large spreadsheet where non-experienced Excel people need to review and add data

At the top, I want to enable an easy way for them to find the relevant section to review or add basic data

The find function can do this but I wanted to make it more simple and thought I could

Have a cell dedicated for them to type in the search criteria (Say cell B1)
Then in cell B2 I want to insert a button that triggers a pre-recorded macro
When recording a macro, in the find section, rather than inserting the search criteria, I want to insert the cell (B1) where the search criteria is located

Is this possible?

I have watched a training video on how to record a macro, but otherwise not very experienced with macros.

hansvogelaar · Accepted Answer

Garth_B&nbsp;
Here is a macro that you can assign to a command button (from the Form Controls section of the Insert dropdown on the Developer tab of the ribbon).
&nbsp;
Sub FindIt()
    Dim rngFind As Range
    If Range("B1").Value = "" Then
        Range("B1").Select
        MsgBox "Please enter a search term, then try again.", vbExclamation
    Else
        Set rngFind = Cells.Find(What:=Range("B1").Value, After:=Range("B1"), _
            LookAt:=xlPart, MatchCase:=False)
        If rngFind.Address = "$B$1" Then
            MsgBox "Text not found :-(", vbExclamation
        Else
            Application.Goto rngFind, True
        End If
    End If
End Sub

Forum Discussion

Using a reference cell for Find function

2 Replies

Resources