String search + page break

[VTdude12]

I need help creating a macro that will:

1) start from cell A1 and search an entire worksheet (column A) for a certain string.

2) the string being search for will be something like "Project:" with a project # following, but I only want to look for the "Project:"

3) insert a page break before that row

4) continue until the end of the worksheet

PLEASE POST ANY QUESTIONS THAT MAY HELP YOU HELP ME!!!

Thanks all!

[shg]

Try this:

Sub x()
    Dim wks         As Worksheet
    Const sWhat     As String = "Project"
    Dim cell        As Range
    Dim sAdr        As String

    Set wks = ActiveSheet

    With wks
        .ResetAllPageBreaks
        With .Columns(1)
            Set cell = .Find(What:=sWhat, After:=.Cells(.Cells.Count), _
                             LookIn:=xlValues, LookAt:=xlPart, _
                             SearchDirection:=xlNext, SearchFormat:=False, _
                             MatchCase:=False, MatchByte:=False)
            If Not cell Is Nothing Then
                sAdr = cell.Address
                Do
                    If cell.row > 1 Then wks.HPageBreaks.Add Before:=cell
                    Set cell = .FindNext(cell)
                Loop Until cell.Address = sAdr
            End If
        End With
    End With
End Sub

[VTdude12]

Thanks for the code!

1 problem though:

I get a run-time error '1004': application-defined or object-defined error on this line of code.

wks.HPageBreaks.Add Before:=cell

Any ideas???

[StephenR]

Do you have "project" in A1? If so, you can't add a page break before row 1. EDIT: should say, needless to say, the code works for me otherwise.

[shg]

Dunno if that's the problem, but good catch, Stephen, thank you.

Prior post corrected.

[VTdude12]

That worked great. Thanks all!

One more thing:

Would it be easier to include this into the same macro or create a seperate one:

1) When it finds "Project" -- in column C of the same row --
=right(cell w/ "project", 9)

2) Copy and paste special that cell (I need it in number format to vlookup against it)

3) In column D of the same row -- =vlookup(# from column C, Sheet1!A:B, 2, FALSE)

Please let me know if you need any further information! THANKS!

[VTdude12]

bump bump bump

[VTdude12]

What would be the best way to include the following into the code that shg already provided to me?

It would probably be best to imbed it into the same loop already present in the macro, correct? But, I've tried a few ways and cannot get it to work... HELP!

1) When it finds "Project" -- in column C of the same row --
=right(cell w/ "project", 9)

2) Copy and paste special that cell (I need it in number format to vlookup against it)

3) In column D of the same row -- =vlookup(# from column C, Sheet1!A:B, 2, FALSE)

Please let me know if you need any further information! THANKS AGAIN ALL!