How do I auto-fill dates that are not consecutive across a row?

[paulfj]

How do I auto-fill dates that are not consecutive across a row? Here's
the situation:

I've created an attendance report for a number of instructors. Near
the top, the instructor enters the dates the class meets for the
semester.

Presuming it meets Mondays and Wednesdays starting on 1/2/06 (Monday),
how can I use Excel (VBA or otherwise) to "auto-fill" the dates,
provided the start and end dates are already entered (either in the
worksheet or in a user form)?

So, in this example, if the start date is 1/2 and the end date is 4/26,
I want the attendance report to auto-fill (starting in J10) 1/2, 1/4,
1/9, 1/11, 1/16 ... 4/26. To slightly add to my problem, the classes
may meet Mon/Wed, Tue/Thu, Mon/Wed/Fri, Mon/Thu, etc.

Any help would be appreciated. I'm new to VBA, but I've been delving
through this newsgroup for about 2 months and a book by John
Walkenbach, both of which have been very helpful in writing a number of
procedures. I'm using Office 2003. Thanks!!

 

[Tom Ogilvy]

Sub AddDates()
Dim startDate As Date
Dim endDate As Date
Dim wkday1 As String, wkday2 As String
Dim w1 As Long, w2 As Long
Dim i As Date, j As Long
Dim v As Variant

wkday1 = "Mon"
wkday2 = "Wed"

startDate = Range("A1").Value
endDate = Range("A2").Value

v = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
w1 = Application.Match(wkday1, v, 0)
w2 = Application.Match(wkday2, v, 0)
Set rng = Range("J1")
j = 0
For i = startDate To endDate
If Weekday(i, vbSunday) = w1 Or _
Weekday(i, vbSunday) = w2 Then
Range("J10").Offset(j, 0) = i
' or Range("J10").offset(0,j) = i
j = j + 1
End If
Next
End Sub


adapt it to your situation

 

[paulfj]

Thanks, Tom! Your code worked like a charm. I adjusted it to go
across rows with the code you provided. I have an additional question
if I may to further clarify.

How do I adjust this code to handle anywhere from 1-6 days depending on
what days the instructor sets for class?

Don't know if I should've done any of this, but ...
I adjusted the strings & added wkday's 3 - 6 (and renamed wkday2 to
"Tue"). I added w3 - w6 w/match formulas, but I can't seem to modify
Weekday function to handle more than 2 entries since the "Or"
apparently only handles 2.

Current code:
Sub AddDates()
Dim startDate As Date
Dim endDate As Date
Dim wkday1 As String, wkday2 As String, wkday3 As String
Dim wkday4 As String, wkday5 As String, wkday6 As String
Dim w1 As Long, w2 As Long
Dim i As Date, j As Long
Dim v As Variant

wkday1 = "Mon"
wkday2 = "Tue"
wkday3 = "Wed"
wkday4 = "Thu"
wkday5 = "Fri"
wkday6 = "Sat"

startDate = Range("A1").Value
endDate = Range("A2").Value

v = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
w1 = Application.Match(wkday1, v, 0)
w2 = Application.Match(wkday2, v, 0)
w3 = Application.Match(wkday3, v, 0)
w4 = Application.Match(wkday4, v, 0)
w5 = Application.Match(wkday5, v, 0)
w6 = Application.Match(wkday6, v, 0)

Set rng = Range("J1")
j = 0
For i = startDate To endDate
If Weekday(i, vbSunday) = w1 Or _
Weekday(i, vbSunday) = w6 Then
'or Range("J10").Offset(j, 0) = i
Range("J10").Offset(0, j) = i
j = j + 1
End If
Next
End Sub

Again, your code worked great; I obviously still have a looong way to
go in learning VBA. Thanks for any help!!

 

[Tom Ogilvy]

Adust this
wkday = Array("Mon", "Wed", "Fri")

to reflect the days of the week.

Sub AddDates()
Dim startDate As Date
Dim endDate As Date
Dim wkday As Variant
Dim w() As Long
Dim i As Date, j As Long, k As Long
Dim v As Variant, bSchoolDay As Boolean

wkday = Array("Mon", "Wed", "Fri")
ReDim w(LBound(wkday) To UBound(wkday))

startDate = Range("A1").Value
endDate = Range("A2").Value

v = Array("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
For i = LBound(wkday) To UBound(wkday)
w(i) = Application.Match(wkday(i), v, 0)
Next
j = 0
For i = startDate To endDate
bSchoolDay = False
For k = LBound(w) To UBound(w)
If Weekday(i, vbSunday) = w(k) Then
bSchoolDay = True
Exit For
End If
Next
If bSchoolDay Then
Range("J10").Offset(0, j) = i
j = j + 1
End If
Next
End Sub

 

[paulfj]

Thanks!! This code is beautiful! I'll probably spend the next couple
weeks figuring it out so I can learn from it, but in the meantime, it
works like a gem.

Now I have to learn UserForms and how to adjust the "wkday = Array()"
based on what the user selects since I don't expect them to enter the
VBE, but I'll see if I can work through this on my own first. Again,
thanks for all your help!

 

[Tom Ogilvy]

Just a hint/approach. You don't have to use an inputbox, that is just a
convenience for demo

answer with M/W/F or MON/WED/FRI or TUE/THU or just WED

Sub TestInput()
Dim res As String, v As Variant
Dim i As Long, s As String
res = InputBox("enter days like T/T or M/W/F")
If res <> "" Then
If InStr(1, res, "/", vbTextCompare) Then
v = Split(res, "/")
Else
ReDim v(0 To 0)
v(0) = res
End If
s = ""
For i = LBound(v) To UBound(v)
s = s & v(i) & vbNewLine
Next
Else
MsgBox "You hit cancel"
End If
MsgBox s
End Sub

 

[paulfj]

just as a follow-up, I'm getting a little closer. Still trying to
decipher the two bits of code you gave so I can adapt them for my use.
They both work great, just trying to make them fit my application. I
appreciate all the help - didn't want you to think I wasn't taking
advantage of it!