Find row number above specific row, then insert page break

I'm having a heck of a time figuring this out. So any help is GREATLY appreciated.

I'm essentially trying to insert a page break on a row that will change from time to time. In this report, range A22:A150 is marked as either 0,1 or 2. 0 means the line is excluded from the report, 1 means the line is included and 2 means include but it is also the last line of a previous section of the report.

If the report cuts off in the middle of a section, I need to find the row where the page break occurs (GOT THAT), then find the row with the previous "2" above that row. Then insert a horizontal page break below that line. I have tried all kinds of methods and I'm just getting nowhere. This is where I am at currently:

Sub findpagebreak()


    Dim ws As Worksheet


    Set ws = Sheets("Quote Sheet")


    With ws.HPageBreaks
        If .Count > 0 Then
            MsgBox .Item(1).Location.Row
        Else
            MsgBox "No Page Breaks on this Sheet"
        End If
    End With

End Sub

Try something like this...

Sub findpagebreak()
    
    Dim ws As Worksheet, rng2 As Range
    
    Set ws = ActiveSheet 'Sheets("Quote Sheet")
    
    With ws.HPageBreaks
        If .Count > 0 Then
            MsgBox .Item(1).Location.Row, , "Page break row"
            Set rng2 = ws.Range("A22:A150").Find(What:="2", _
                                           After:=ws.Range("A" & .Item(1).Location.Row), _
                                           LookIn:=xlValues, _
                                           LookAt:=xlWhole, _
                                           SearchOrder:=xlByRows, _
                                           SearchDirection:=xlPrevious, _
                                           MatchCase:=False)
            If Not rng2 Is Nothing Then
                MsgBox rng2.Row, , "Previous '2' row"
                .Add rng2.Offset(1)
            Else
                MsgBox "No '2' found previous to the PageBreak."
            End If
                                      
        Else
            MsgBox "No Page Breaks on this Sheet"
        End If
    End With
    
End Sub

Originally Posted by mvparker79

It tells me the page break row, but then I get a "Type Mismatch" error on the set rng2 line.

I edited the "A22:A150" range in the original post, try it again.

You're welcome. Glad it worked.