I am trying to build vb code that will fill in days based upon an input
month and year. For example, if I were to choose September, 2005, a
newly created sheet would fill in the first row as follows:

Sept 1, Sept 2, Sept 5, Sept 6... (excluding 3 & 4 as weekends)

I know I need to write a loop that checks the date and validates it
based upon weekday(excluding if 1 or 7) and stopping if the last day of
the month is reached, but I'm not really sure how to det it up.

Any help would be greatly appreciated.

Thanks,

Jeremy

Dim mth As Long
Dim yr As Long
Dim dte As Date
Dim i As Long
Dim j As Long

mth = 9
yr = 2005

For i = 1 To 31
dte = DateSerial(yr, mth, i)
If Month(dte) = Month(DateSerial(yr, mth, 1)) Then
If Weekday(dte, 2) < 6 Then
j = j + 1
Cells(1, j).Value = dte
Cells(1, j).NumberFormat = "mmm d"
End If
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"jaezif" wrote in message
ups.com...
I am trying to build vb code that will fill in days based upon an input
month and year. For example, if I were to choose September, 2005, a
newly created sheet would fill in the first row as follows:

Sept 1, Sept 2, Sept 5, Sept 6... (excluding 3 & 4 as weekends)

I know I need to write a loop that checks the date and validates it
based upon weekday(excluding if 1 or 7) and stopping if the last day of
the month is reached, but I'm not really sure how to det it up.

Any help would be greatly appreciated.

Thanks,

Jeremy

Here is 2 ways you can do it:

Using a loop

Sub BB()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim yr As Long, mnth As Long
Dim rw As Long
yr = 2005
mnth = 5

rw = 1
dtStart = DateSerial(yr, mnth, 1)
If Weekday(dtStart, 2) 5 Then _
dtStart = dtStart + (8 - Weekday(dtStart, 2))
dtEnd = DateSerial(yr, mnth + 1, 0)
If Weekday(dtEnd, 2) 5 Then _
dtEnd = dtEnd - (Weekday(dtEnd, 2) - 5)
For dt = dtStart To dtEnd
If Weekday(dt, 2) <= 5 Then
Cells(rw, "A") = dt
rw = rw + 1
End If
Next
End Sub


use the built in DataSeries command

Sub CC()
Dim dtStart As Date
Dim dtEnd As Date
Dim dt As Date
Dim yr As Long, mnth As Long
Dim rw As Long
yr = 2005
mnth = 4

rw = 1
dtStart = DateSerial(yr, mnth, 1)
If Weekday(dtStart, 2) 5 Then _
dtStart = dtStart + (8 - Weekday(dtStart, 2))
dtEnd = DateSerial(yr, mnth + 1, 0)
Range("A1") = dtStart
Range("A1").DataSeries Rowcol:=xlColumns, _
Type:=xlChronological, Date:=xlWeekday, _
Step:=1, Stop:=dtEnd, Trend:=False
End Sub


--
Regards,
Tom Ogilvy

"jaezif" wrote in message
ups.com...
I am trying to build vb code that will fill in days based upon an input
month and year. For example, if I were to choose September, 2005, a
newly created sheet would fill in the first row as follows:

Sept 1, Sept 2, Sept 5, Sept 6... (excluding 3 & 4 as weekends)

I know I need to write a loop that checks the date and validates it
based upon weekday(excluding if 1 or 7) and stopping if the last day of
the month is reached, but I'm not really sure how to det it up.

Any help would be greatly appreciated.

Thanks,

Jeremy