Enter Date Range using Input Box

[Ged Traynor]

Hi All

Not really sure if this even possible, but if I don't ask I'll never know. I'm wondering is it possible to get the user to enter a date range into an Input Box and populate the range in column A, I'd imagine this can be done, but here's the sting in the tell, I don't want Saturdays and Sundays in the Column.

So if the user enters the following date range
01/06/2011 to 30/06/2011 it will populate column A with the following, as you can see from below Saturdays and Sundays are missing.

01/06/2011
02/06/2011
03/06/2011
06/06/2011
07/06/2011
08/06/2011
09/06/2011
10/06/2011
13/06/2011
14/06/2011
15/06/2011
16/06/2011
17/06/2011
20/06/2011
21/06/2011
22/06/2011
23/06/2011
24/06/2011
27/06/2011
28/06/2011
29/06/2011
30/06/2011

Thanks in advance

 

[VoG]

Try

Sub EnterDates()
Dim x
Dim d1 As Date, d2 As Date, d3 As Date, i As Long
x = Split(InputBox("Enter start date and end date separated by comma"), ",")
d1 = DateValue(x(0))
d2 = DateValue(x(1))
For i = 1 To d2 - d1 + 1
    d3 = d1 + i - 1
    If Weekday(d3, vbMonday) < 6 Then Range("A" & Rows.Count).End(xlUp).Offset(1).Value = d3
Next i
End Sub

 

[Ged Traynor]

Thank you Soooooooooooooo much VoG

Simply amazing

Ged

 

[Ged Traynor]

That works fine and thanks again, there's only one little snag, when column A is populated it starts of on Row 2 and not Row 1
Can this be amended so that it populates A1 first instead of A2

Cheers
Ged

 

[VoG]

Try

Sub EnterDates()
Dim x
Dim d1 As Date, d2 As Date, d3 As Date, i As Long
x = Split(InputBox("Enter start date and end date separated by comma"), ",")
d1 = DateValue(x(0))
d2 = DateValue(x(1))
Range("A1").Value = d1
For i = 2 To d2 - d1 + 1
    d3 = d1 + i - 1
    If Weekday(d3, vbMonday) < 6 Then Range("A" & Rows.Count).End(xlUp).Offset(1).Value = d3
Next i
End Sub

 

[Ged Traynor]

Excellent much appreciated for all your help VoG