Sub Maintenance()
Dim rngFound As Range, FirstFound As String
Dim tc As Date, i As Long, counter As Long
Application.ScreenUpdating = False
Set rngFound = Columns("I").Find("Maintenance", [I1], xlValues, xlWhole, 1, 1, 0)
If Not rngFound Is Nothing Then
FirstFound = rngFound.Address
Do
tc = rngFound.Offset(, 3).Value 'Ticket closed
For i = rngFound.Row + 1 To Range("I" & Rows.Count).End(xlUp).Row + 1
If Range("K" & i).Value >= tc Or Range("K" & i).Value = Empty Then
rngFound.EntireRow.Copy
Rows(i).Insert
Range("I" & i).Resize(, 3).Value = Array("Out of Maintenance", "", tc)
counter = counter + 1
Set rngFound = Range("I" & i)
Exit For
End If
Next i
Set rngFound = Columns("I").FindNext(After:=rngFound)
Loop Until rngFound.Address = FirstFound
End If
Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox counter & " rows inserted.", vbInformation, "Maintenance Tickets"
End Sub
Bookmarks