Insert dates from a userform

[leonidas]

Hi,

I have a userform with textbox3 and textbox4. The user can insert a
startdate in textbox3 and insert an enddate in textbox4. The code below
fills cells B10 and below with the dates between the startdate and
enddate and the weekends are excluded. The only problem is that by
excluding the weekends the enddate automatically changes to a later
date.
What should I change to get the enddate as last date in column B and
still exclude the weekends? Thanks in advance!


Code:
--------------------
Range("B10").Select
With Selection
.Value = CDate(TextBox3.Text)
.NumberFormat = "dd/mm/yyyy"
.AutoFill .Resize(CDate(TextBox4.Text) - CDate(TextBox3.Text) _
+ 1), Type:=xlFillWeekdays
End With

 

[Arif Ali]

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