Insert New Row after finding last Occurrence Of Text Value in Col C

[sspatriots]

Good morning,

I'm trying to use the code below that I found to insert a new row in a table just below the last occurrence of finding "1 AJW" in column C. This code I copied and tried to manipulate was originally set up up to insert two rows. I only need to insert 1 row. I also don't quite get what the ".Value" and ".Offset" are doing in the code or how to adjust for my situation.

Sub InsertNewJob
Dim Lst As Integer, n As Long, Txt As String, Temp As String, R As Range
Lst = Range("C" & Rows.Count).End(xlUp).Row
For n = Lst To 2 Step -1
With Range("C" & n)
Txt = ""
Select Case True

Case Left(.Value, 2) & .Offset(, 5).Value = "1 AJW"
If IsNumeric(Mid(.Value, 3, 1)) Then
Txt = "1 AJW"

End If
Case Left(.Value, 3) & .Offset(, 5).Value = "1 AJW"
End Select


If Not Txt = Temp Then
.Offset(1).Resize(2).EntireRow.Insert
Temp = Txt
End If


End With

End Sub


Thanks, SS

 

[rlv01]

One way.

Sub InsertNewJob()

    Dim R As Range, rng As Range, rngFirst As Range, rngLast As Range
        
    Set rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
    
    Set rngFirst = rng.Find(What:="1 AJW", Lookat:=xlWhole, MatchCase:=True)
    If Not rngFirst Is Nothing Then
        Set R = rngFirst
        Do While Not R Is Nothing
            Set rngLast = R
            Set R = rng.Find(What:="1 AJW", After:=R, Lookat:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
            If R.Address = rngFirst.Address Then
                Set R = Nothing
                Exit Do
            End If
        Loop
        rngLast.Offset(1).EntireRow.Insert Shift:=xlDown
    End If
End Sub

 

[sspatriots]

That seems to work, but how do I shift the focus to go to that first cell in that row so the user can make their entry? Even better scroll that row to the top of the table at the same time.

 

[rlv01]

Sub InsertNewJob()
    Dim R As Range, rng As Range, rngFirst As Range, rngLast As Range
    
    Set rng = Range("C2", Range("C" & Rows.Count).End(xlUp))
    
    Set rngFirst = rng.Find(What:="1 AJW", Lookat:=xlWhole, MatchCase:=True)
    If Not rngFirst Is Nothing Then
        Set R = rngFirst
        Do While Not R Is Nothing
            Set rngLast = R
            Set R = rng.Find(What:="1 AJW", After:=R, Lookat:=xlWhole, MatchCase:=False, SearchDirection:=xlNext)
            If R.Address = rngFirst.Address Then
                Set R = Nothing
                Exit Do
            End If
        Loop
        With rngLast.Offset(1)
            .Select
            .EntireRow.Insert Shift:=xlDown
        End With
    End If
End Sub

 

[sspatriots]

That's perfect. Thank you. This will work great