Using a reference cell for Find function

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

Formulas and Functions

Macros and VBA

HansVogelaar

Jan 25, 2021

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

HansVogelaar

MVP

Jan 25, 2021

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

Garth_B

Copper Contributor

Jan 25, 2021

HansVogelaar

Thanks, Hans

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

Cheers Garth

Forum Discussion

Using a reference cell for Find function

Resources