need help. page breaks

Dan · 03-11-2005, 11:06 PM

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 · 03-12-2005, 12:06 AM

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

"Dan" wrote:

> 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
>
>

Dave Peterson · 03-12-2005, 01:06 AM

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 wrote:
>
> 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

--

Dave Peterson

Dan · 03-12-2005, 01:06 AM

Thanks Dave and gocush. Its nice to know that you and
other experts are out there to lend a hand. Dan
>-----Original Message-----
>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
>
>.
>

J_J · 03-12-2005, 05:06 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
>>
>> 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
>
> --
>
> Dave Peterson

Dave Peterson · 03-12-2005, 11:06 AM

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?

J_J wrote:
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Tom Ogilvy · 03-12-2005, 11:06 AM

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

"J_J" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> >>
> >> 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
> >
> > --
> >
> > Dave Peterson
>
>

Dave Peterson · 03-12-2005, 11:06 AM

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

Tom Ogilvy wrote:
>
> 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
>
> "J_J" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > >>
> > >> 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
> > >
> > > --
> > >
> > > Dave Peterson
> >
> >

--

Dave Peterson

Dan · 03-14-2005, 01:07 AM

Thanks, all. i changed all my StartRows from Dave's
original reply and it worked great. Thanks for the help.
Dan
>-----Original Message-----
>Oops. Thanks for the correction. (Bad test data made it
work ok.)
>
>Tom Ogilvy wrote:
>>
>> 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
>>
>> "J_J" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in
message
>> > news:[email protected]...
>> > > 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 wrote:
>> > >>
>> > >> 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
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> >
>> >
>
>--
>
>Dave Peterson
>.
>