Find last cell with matching value
 

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.

Find last cell with matching value
 

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)

"Billy B" wrote in message
...
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.

Find last cell with matching value
 

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

--
Gary''s Student - gsnu200747


"Billy B" wrote:

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.

Find last cell with matching value
 

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