Using a reference cell for Find function

[Garth_B]

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

1. Have a cell dedicated for them to type in the search criteria (Say cell B1)
2. Then in cell B2 I want to insert a button that triggers a pre-recorded macro
3. 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.

[#2 · Accepted solution]

@Garth_B 

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).

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

[#3]

@Hans Vogelaar 

Thanks, Hans

I blundered my way through inserting VBA code and it works beautifully

Cheers Garth