Finding the last cell that matches criteria

[brites]

Hi folks!

Heres the deal: I have in column B a list (ascending) of status (ok, sold, out of order, damage, rent).
I wanna know what is the last row where "out of order" appears.
What is the code line for that?

Thx in advance!

 

[Jonmo1]

How's this

=LOOKUP(2,1/(B1:B100="out of order"),ROW(B1:B100))

 

[VoG]

By "code line" do you mean VBA? If so

Sub FindLast()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("B" & i).Value = "out of order" Then Exit For
Next i
MsgBox "Found in row " & i
End Sub

Note: this will erroneously return 1 if "out of order" is not found.

 

[brites]

Hi Jonmo1, thanks for your help.
Actually I wanna do it inside my VBA and store it under "variable y".

To find the first one im using:
x = Application.WorksheetFunction.Match("out of order", Sheets("Management_DB").Columns(2), 0)

Something like that....

 

[brites]

ADVERTISEMENT

VOG II,
thanks! that did it!
Im already using it... but just for education wise...

Isnt there a simple line like the one to find the first one via ".match"?

 

[VoG]

Isnt there a simple line like the one to find the first one via ".match"?

There may be but I don't know how...

 

[erik.van.geit]

Isnt there a simple line like the one to find the first one via ".match"?

Hi,

yes there is
see EVALUATE in the helpfiles

Evaluate("=LOOKUP(2,1/(B1:B100=""out of order""),ROW(B1:B100))")

kind regards,
Erik

 

[brites]

Alright!

Ty!