Insert page break btw Sat and Mon dates

D

Dan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top