Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find next up matching cell? | Excel Worksheet Functions | |||
find cell with matching text, then reference cell in same row | Excel Worksheet Functions | |||
Find Matching Cell and Copy Cell Content in same Row | Excel Programming | |||
find matching cell in spread over two worksheets | Excel Programming | |||
Find row with matching cell and retrieve values (VBA) | Excel Programming |