Find Last Instance Of A Word

[Jaye7]

Hi All,

I am after a script that will find the last instance of the word Yes within Column A of a worksheet.

The word Yes may be in column A 1000+ times and there are blank/ empty cells (non contiguous) in column A also.

I would run the code using a macro button on the spreadsheet.
I have several buttons that look for different words but I can modify a suitable script.

Thanks

 

[Jaye7]

Thanks everyone,

rsxchin and didi's examples are what I am after, is there a way to make it non case sensitive as some results may be Yes or YES or yes.

Thanks.

 

[diddi]

use UCase() function:

Sub LastYes ()
    for row=Cells(Rows.Count,1).End(xlUp).Row to 1 step -1
        if ucase(cells(row,1))="YES" then
            msgbox "last Yes is in row " &row
            exit for
        end if
    next row
end sub

 

[Jaye7]

beautiful, thanks Diddi, exactly what I am after, have a great weekend in Shepparton.

 

[Jaye7]

Sorry Diddi, one last thing.

Can you please provide the code which will select the last cell which contains the yes also as that's actually what I am after.

 

[Sandeep Warrier]

Before the Exit For line use

Cells(row,1).Select

 

[Trebor76]

Hi Jaye7,

Just though I'd let you that Tom's code works really well, just need to change the reference from 'No' to 'Yes'

Regards,

Robert

 

[Jaye7]

Excellent, thanks Sandeep.warrier, now it is perfect.

 

[Jaye7]

Thanks for your time everyone, much appreciated.

 

[Trebor76]

Thought I'd throw in my two cents worth (based in part from Tom's earlier posting):

Sub FindLast()

    Dim varMySearchItem As Variant
    Dim lngLastRow As Long
    
    varMySearchItem = "Yes"
    lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    If Application.WorksheetFunction.CountIf(Range("A1:A" & lngLastRow), varMySearchItem) > 0 Then
        Range("A" & Evaluate("MAX(IF(A1:A" & lngLastRow & "=""" & varMySearchItem & """,ROW(A1:A" & lngLastRow & ")))")).Select
    Else
        MsgBox "There were no matches for " & varMySearchItem & " found!!", vbExclamation, "Last Instance Editor"
    End If
    
End Sub

Regards,

Robert