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

 

[RSXchin]

sub last_yes()
lastrow = cells(rows.count, 1).end(xlup).row
for i = lastrow to 1 step -1
if range("a" & i).text = "YES" then
     msgbox ("The last yes in Column A is in row " & i)
end if
next i
end sub

 

[diddi]

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

 

[RSXchin]

Beat you this time Didi
I didn't know you didn't have to declare lastrow...

 

[JoeMo]

Here's an alternative that may be faster if you have a large number of populated cells in column A:

Sub FindLastInstance()
Dim findWhat As String
Dim sRng As Range, fRng As Range
Dim lRw As Long
findWhat = "Yes"
lRw = Range("A" & Rows.Count).End(xlUp).Row
Set sRng = Range("A1", "A" & lRw + 1)
Set fRng = sRng.Find(findWhat, after:=Cells(lRw + 1, 1), lookat:=xlWhole, searchdirection:=xlPrevious)
MsgBox "Last instance of " & findWhat & " is in cell " & fRng.Address

End Sub

 

[diddi]

same time on the posts. have to call it a draw
have to get the admin to start putting seconds on the posttimes

 

[diddi]

oh and its worth noting the use of

exit for

this saves heaps of time if Yes is near the bottom

 

[RSXchin]

*facepalm*

I don't even have VB open right now. Probably should have used it though.

JoeMo, what about your code is faster? (not a mean question, I swear)

 

[Sandeep Warrier]

Also, how about

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;">17</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">Yes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C1</th><td style="text-align:left">=LOOKUP(<font color="Blue">2,1/(<font color="Red">$A$1:$A$20="Yes"</font>),ROW(<font color="Red">$A$1:$A$20</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[Tom Urtis]

This will also do it, sans looping:

With Cells(Rows.Count, 1).End(xlUp)
MsgBox "A" & Evaluate("MAX(IF(A1:A" & .Row & "=""No"",ROW(A1:A" & .Row & ")))")
End With