Amend Macro to use data in current cell as the search criteria..

[Craigside]

Hi

I have a new macro which is great! However I also need a different version.

I want to click on cell containing 'Ref No', activate macro, and macro use the cell content as the data, and doesn't use the "Enter Reference Number" box?

Thank you...

Sub Find_Ref_Number()

    

    Dim strRefNum As String, rngFound As Range, rngShtName As Range

    

    strRefNum = Application.InputBox("Enter Reference Nummber", "Search for Reference Number", Type:=2)

    If strRefNum = "False" Then Exit Sub  'User Canceled

    

    'Look through sheets list on Sheet("Locations")

    For Each rngShtName In Sheets("Locations").Range("A10", Sheets("Locations").Range("A" & Rows.Count).End(xlUp))

    

        'Search for Ref Number on each sheet in list

        Set rngFound = Sheets(rngShtName.Value).Cells.Find(What:=strRefNum, _

                                                           LookIn:=xlValues, _

                                                           LookAt:=xlWhole, _

                                                           SearchOrder:=xlColumns, _

                                                           SearchDirection:=xlNext, _

                                                           MatchCase:=False)

        'If found, go to match

        If Not rngFound Is Nothing Then

            Application.Goto rngFound, Scroll:=True

            rngFound.Resize(9, 6).Offset(-1, -5).Select

            Exit For

        End If

        

    Next rngShtName

    

    'If no match found on any sheet, display message

    If rngFound Is Nothing Then MsgBox strRefNum, vbExclamation, "No Match Found"

    

End Sub