SOLVED

Using a reference cell for Find function

%3CLINGO-SUB%20id%3D%22lingo-sub-2095873%22%20slang%3D%22en-US%22%3EUsing%20a%20reference%20cell%20for%20Find%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2095873%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20spreadsheet%20where%20non-experienced%20Excel%20people%20need%20to%20review%20and%20add%20data%3C%2FP%3E%3CP%3EAt%20the%20top%2C%20I%20want%20to%20enable%20an%20easy%20way%20for%20them%20to%20find%20the%20relevant%20section%20to%20review%20or%20add%20basic%20data%3C%2FP%3E%3CP%3EThe%20find%20function%20can%20do%20this%20but%20I%20wanted%20to%20make%20it%20more%20simple%20and%20thought%20I%20could%3C%2FP%3E%3COL%3E%3CLI%3EHave%20a%20cell%20dedicated%20for%20them%20to%20type%20in%20the%20search%20criteria%20(Say%20cell%20B1)%3C%2FLI%3E%3CLI%3EThen%20in%20cell%20B2%20I%20want%20to%20insert%20a%20button%20that%20triggers%20a%20pre-recorded%20macro%3C%2FLI%3E%3CLI%3EWhen%20recording%20a%20macro%2C%20in%20the%20find%20section%2C%20rather%20than%20inserting%20the%20search%20criteria%2C%20I%20want%20to%20insert%20the%20cell%20(B1)%20where%20the%20search%20criteria%20is%20located%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIs%20this%20possible%3F%3C%2FP%3E%3CP%3EI%20have%20watched%20a%20training%20video%20on%20how%20to%20record%20a%20macro%2C%20but%20otherwise%20not%20very%20experienced%20with%20macros.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2095873%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2095968%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20reference%20cell%20for%20Find%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2095968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F942375%22%20target%3D%22_blank%22%3E%40Garth_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20a%20macro%20that%20you%20can%20assign%20to%20a%20command%20button%20(from%20the%20Form%20Controls%20section%20of%20the%20Insert%20dropdown%20on%20the%20Developer%20tab%20of%20the%20ribbon).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20FindIt()%0A%20%20%20%20Dim%20rngFind%20As%20Range%0A%20%20%20%20If%20Range(%22B1%22).Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20Range(%22B1%22).Select%0A%20%20%20%20%20%20%20%20MsgBox%20%22Please%20enter%20a%20search%20term%2C%20then%20try%20again.%22%2C%20vbExclamation%0A%20%20%20%20Else%0A%20%20%20%20%20%20%20%20Set%20rngFind%20%3D%20Cells.Find(What%3A%3DRange(%22B1%22).Value%2C%20After%3A%3DRange(%22B1%22)%2C%20_%0A%20%20%20%20%20%20%20%20%20%20%20%20LookAt%3A%3DxlPart%2C%20MatchCase%3A%3DFalse)%0A%20%20%20%20%20%20%20%20If%20rngFind.Address%20%3D%20%22%24B%241%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20MsgBox%20%22Text%20not%20found%20%3A-(%22%2C%20vbExclamation%0A%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.Goto%20rngFind%2C%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2096101%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20a%20reference%20cell%20for%20Find%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2096101%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20Hans%3C%2FP%3E%3CP%3EI%20blundered%20my%20way%20through%20inserting%20VBA%20code%20and%20it%20works%20beautifully%3C%2FP%3E%3CP%3ECheers%20Garth%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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 Replies
best response confirmed by Garth_B (New Contributor)
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

@Hans Vogelaar 

Thanks, Hans

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

Cheers Garth