Change "Text Like" to "Greater than"

[J.W. Aldridge]

This code works only if there is a 00:30 time given.
I need to search for the value that is greater than 00:30.
List of times may or may not include 00:30 exactly, but at the point
where it is greater than 00:30, I want to insert a line.

Possible Example:
00:28:00
00:29:02
00:31:01
00:32:06

The line inserted would be between 00:29:02 and 00:31:01.


Sub InsRow()
Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("F" & i).Text Like "*00:30*" Then
Rows(i + 1).Insert
Exit For
End If
Next i
End Sub

 

[Ken]

Jeremy

Try something like:

Sub InsRow()

Dim LR As Long, i As Long
LR = Range("F" & Rows.Count).End(xlUp).Row

For i = 1 To LR

If Range("F" & i).Value > (1 / 24 / 2) Then
Rows(i).Insert
Exit For
End If

If Range("F" & i).Value = (1 / 24 / 2) Then
Rows(i + 1).Insert
Exit For
End If

Next i

End Sub

The value of =1/24/2 corresponds to your half hour time. I assumed
you want to insert the line after and exact match, which necessitates
the two options (i or i+1) for the entry point for the new row.

Good luck

Ken
Norfolk, Va

 

[J.W. Aldridge]

Mr. Ken,


Unfortunately, this only added rows above my header row....

Thanx anyways...

 

[Ken]

Is the value after which you want to insert a row exactly a half hour?
(00:30:00). Are there values above the top of the data that could
have numbers, that trigger the row insertion and the end of the
macro? You may want to try

For i=x to LR, where x is the top row of your data; that way you
should never have rows inserted above the header.

Ken