Change "Text Like" to "Greater than"

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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
 
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
 
Mr. Ken,


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

Thanx anyways...
 
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
 
Back
Top