Excel Find Method in Excel VBA (Any version of Excel)

[manandpc]

Here is a subroutine that can be used to find the last cell in a sheet that contains a string.

First, make it similar to the Ctrl-F (excel default find), but more simple
Second, adjust the where about to be in the center (sort of) on the screen of the cell found!

Note: if not found, do nothing, that can be improve!

Sub FindLast()
    Static sWhat
    sWhat = InputBox("Find What:", "FIND LAST", sWhat)
    If sWhat <> "" Then
        Dim rFound As Range
        
        On Error Resume Next
        Set rFound = Cells.Find(What:=sWhat, _
            After:=Cells(Cells.Rows.Count, Cells.Columns.Count), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
            SearchDirection:=xlPrevious, MatchCase:=False, SearchFormat:=False)
        On Error GoTo 0
        
        If Not rFound Is Nothing Then
            ' MsgBox rFound.Row & " " & rFound.Column
            iRowOffset = 10
            rFound.Offset(iRowOffset, 0).Select
            If rFound.Row <= iRowOffset Then iRowOffset = rFound.Row - 1
            rFound.Offset(-iRowOffset, 0).Select
            iColOffset = 4
            rFound.Offset(0, iColOffset).Select
            If rFound.Column <= iColOffset Then iColOffset = rFound.Column - 1
            rFound.Offset(0, -iColOffset).Select
            rFound.Select
            
            ' sYesNo = InputBox("Copy the last month 3 columns ? (Y)", "COPY LAST MONTH")
            ' If sYesNo = "y" Or sYesNo = "Y" Then
            '    Range(rFound.Offset(0, -2), rFound.Offset(0, 0)).Select
            '    Application.Selection.Copy
            '    rFound.Offset(0, 1).Select
            '    Application.ActiveSheet.Paste
            '    rFound.Offset(0, 1).Select
            '    Application.CutCopyMode = False
            ' End If
        End If
    End If
End Sub