Insert dates from a userform
It's a little more work than your approach, but it works, and you should be
able to adapt this to your needs...
Private Sub Dates()
Dim NumDays, DayOfWeek, CurrentRow, i As Integer
Dim StartD, EndD As Date
StartD = CDate(Textbox3.text)
EndD = CDate(Textbox4.text)
CurrentRow = 10
'Numdays is the actual number of days
NumDays = DateDiff("d", StartD, EndD)
For i = 0 To NumDays
'Use function to identify ordinal day of week with Monday = 1
DayOfWeek = Weekday(DateAdd("d", i, StartD), vbMonday)
'if day of week is not Sat(6) or Sun(7) then write the date and increment
row
If DayOfWeek < 6 And DayOfWeek < 7 Then
Range("B" & CStr(CurrentRow)).Value = DateAdd("d", i, StartD)
CurrentRow = CurrentRow + 1
End If
'check next date in range
Next i
End Sub
|