Find last cell with matching value

[Guest]

I have a column (column C) containing data with a custom format of h:ss. I
need to find the last 8:00 in the column and then insert a row below that
cell. I have looked in Walkenback's Books and the internet and unable to find
how to ge started with this. Any suggestions?

Thank you.

 

[Bob Phillips]

This gives you the row number

=MAX(IF(A1:A20=TIME(8,0,0),ROW(A1:A20)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

If you need to automate this:

Sub Billy_B()
Dim n As Long, i As Long
x = Evaluate("=time(8,0,0)")
n = Cells(Rows.Count, "L").End(xlUp).Row
For i = n To 1 Step -1
With Cells(i, "L")
If .Value = x Then
.Offset(1, 0).EntireRow.Insert
Exit Sub
End If
End With
Next
End Sub

 

[Bernd P]

Hello,

Another VBA approach:

Sub InsRowAfterLast8()
Dim r As Range
Set r = Range("C:C").Find(What:="8:00", _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious)
If r Is Nothing Then Exit Sub
r.Offset(1, 0).EntireRow.Insert
End Sub

Regards,
Bernd