need help. page breaks

[Dan]

From a previous post. Thanks for taking a look.

I am trying to check a list of dates in column A starting
at A2 (variable range). I want to step down the column and
check the first date to the second, second to the third
and so on. the only condition I am looking to check is
that if the first date is a Saturday and the second date
is a Monday (no Sundays in range)then insert a page break
before Monday or after Saturday. I got myself tangled up,
see code below. Thanks, Dan

Sub AddBreaks()
Worksheets("Final").Range("A1").Select
Dim StartRow As Integer
Dim FinalRow As Integer
Dim FVal As Date
Dim FirstVal As Integer
Dim NVal As Date
Dim NextVal As Integer
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow
FVal = Cells(i, 0).Value
MsgBox FVal
NVal = Cells(StartRow + 1, 0).Value
MsgBox NVal
FirstVal = Weekday(FVal)
MsgBox FirstVal
NextVal = Weekday(NVal)
MsgBox NextVal
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells
(StartRow + 1, 0)
End If
FVal = NVal
MsgBox FVal
Next i
End Sub

 

[gocush]

Try this:

Sub AddBreaks()
Worksheets("Final").Range("A1").Select
Dim StartRow As Integer
Dim FinalRow As Integer

Dim FirstDate As Date
Dim NextDate As Date

Dim FirstVal As Integer
Dim NextVal As Integer

Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow
FirstDate = Cells(i, 0).Value
MsgBox FirstDate
NextDate= Cells(i+ 1, 0).Value
MsgBox NextDate
FirstVal = Weekday(FirstDate)
MsgBox FirstVal
NextVal = Weekday(NextDate)
MsgBox NextVal
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells
(i+ 1, 0)
End If
Next i
End Sub

 

[Dave Peterson]

FVal = Cells(i, 0).Value
is gonna cause trouble. There is no column 0.

I changed that column 0 to column 1 (column A) and one more typo:
ActiveSheet.HPageBreaks.Add before:=Cells(StartRow + 1, 0)
to
ActiveSheet.HPageBreaks.Add before:=Cells(i + 1, 0)

And it worked fine.

You were always adding the page break to the startrow+1--not the rows you were
looping through.

Option Explicit
Sub AddBreaks()
Worksheets("Final").Range("A1").Select
Dim StartRow As Long
Dim FinalRow As Long
Dim FVal As Date
Dim FirstVal As Long
Dim NVal As Date
Dim NextVal As Long
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow
FVal = Cells(i, 1).Value
'MsgBox FVal
NVal = Cells(StartRow + 1, 1).Value
' MsgBox NVal
FirstVal = Weekday(FVal)
'MsgBox FirstVal
NextVal = Weekday(NVal)
'MsgBox NextVal
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells(i + 1, 1)
End If
FVal = NVal
'MsgBox FVal
Next i
End Sub

 

[Dan]

Thanks Dave and gocush. Its nice to know that you and
other experts are out there to lend a hand. Dan

 

[J_J]

Hi Dave,
I've filled A2:A30 with following dates (and make sure that at least one
Saturday is followed by Monday) and tried your code on my WinXP+XL2003
system. Although get no errors in executing the macro, I do not see any
visible pagebreaks either (where it should be -day7 followed by day2) on
any view mode of the active sheet later. Am I missing something here?
Regards
J_J

 

[Dave Peterson]

I saw the page breaks immediately when I did it (winxp and xl2003, too).

If you do file|print preview, does it look nice?

If no, did you put real dates in those cells--or text that looks like dates?

 

[Tom Ogilvy]

replacing "StartRow" with "i" needed to be done in two places. Dave must
have forgotten to change it in the first place before posting.

Sub AddBreaks()
Worksheets("Final").Range("A1").Select
Dim StartRow As Long
Dim FinalRow As Long
Dim FVal As Date
Dim FirstVal As Long
Dim NVal As Date
Dim NextVal As Long
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow
FVal = Cells(i, 1).Value
'MsgBox FVal
NVal = Cells(i + 1, 1).Value
' MsgBox NVal
FirstVal = WeekDay(FVal)
'MsgBox FirstVal
NextVal = WeekDay(NVal)
'MsgBox NextVal
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells(i + 1, 1)
End If
FVal = NVal
'MsgBox FVal
Next i
End Sub

--
Regards,
Tom Ogilvy

Hi Dave,
I've filled A2:A30 with following dates (and make sure that at least one
Saturday is followed by Monday) and tried your code on my WinXP+XL2003
system. Although get no errors in executing the macro, I do not see any
visible pagebreaks either (where it should be -day7 followed by day2) on
any view mode of the active sheet later. Am I missing something here?
Regards
J_J

 

[Dave Peterson]

Oops. Thanks for the correction. (Bad test data made it work ok.)

replacing "StartRow" with "i" needed to be done in two places. Dave must
have forgotten to change it in the first place before posting.

Sub AddBreaks()
Worksheets("Final").Range("A1").Select
Dim StartRow As Long
Dim FinalRow As Long
Dim FVal As Date
Dim FirstVal As Long
Dim NVal As Date
Dim NextVal As Long
Dim i As Integer

StartRow = 2
FinalRow = Range("A65536").End(xlUp).Row

For i = StartRow To FinalRow
FVal = Cells(i, 1).Value
'MsgBox FVal
NVal = Cells(i + 1, 1).Value
' MsgBox NVal
FirstVal = WeekDay(FVal)
'MsgBox FirstVal
NextVal = WeekDay(NVal)
'MsgBox NextVal
If (FirstVal = 7) And (NextVal = 2) Then
ActiveSheet.HPageBreaks.Add before:=Cells(i + 1, 1)
End If
FVal = NVal
'MsgBox FVal
Next i
End Sub

 

[Dan]

Thanks, all. i changed all my StartRows from Dave's
original reply and it worked great. Thanks for the help.
Dan