Using Visual Basic, insert multiple page breaks in Excel after the specific text "break" appears in Column L

Question

How to insert multiple page breaks in Excel after the specific text "break" appears in Column L.
There are 266 "break"s needed and there are approximately 6000 rows.

I know how to do this if the word "break" was in column A but since "break" is in column L now, it isn't working.

This is what I was using:

___

Sub Macro1()

---

For i = 1 To 6000

---

If Cells(i, 1) = "break" Then

---

**ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(i + 1, 1)** 

---

End If

---

Next i

---

End Sub

___

Any assistance would be appreciated.

Answer 1

Column L is the 12th column, so change

If Cells(i, 1) = "break" Then

to

If Cells(i, 12) = "break" Then

A more efficient version:

Sub Macro1()
    Dim rng As Range
    Dim adr As String
    Set rng = Range("L:L").Find(What:="break", LookAt:=xlWhole)
    If Not rng Is Nothing Then
        adr = rng.Address
        Do
            ActiveSheet.HPageBreaks.Add Before:=rng.Offset(1)
            Set rng = Range("L:L").Find(What:="break", After:=rng, LookAt:=xlWhole)
            If rng Is Nothing Then Exit Do
        Loop Until rng.Address = adr
    End If
End Sub