Autofill dates excluding certain weekdays

[Barruel]

Hi.

First post here, so I salute everyone in this forum.

I'm not too savvy when it comes to Excel, but I've always been able to solve stuff (even complex stuff) just by googling a bit about it. This time is different and I've just had to register in this forum seeking help. I've even searched this forum for my issue but found nothing useful.

I'm a teacher in an high school. I have a number of groups which have classes at certain workdays but not in every workday. I have for example class x on mondays, tuesdays and thursdays, class y on tuesdays, wednesdays and freedays, and so on.

I need to log certain stuff for every class, and I want Excel to autofill me a list of dates when I have class x (or y, or z).

How can I tell Excel to show me a list of dates which include only certain workdays (say mondays, tuesdays and thursdays)? Is this possible?

TIA

PS: I'm working with Excel 2000, but I own also Office 2007, so any working solution will be OK.

 

[Barruel]

not perfect? cant have that

Well, you just did it. Thanks again.

 

[diddi]

happy chalking! (well it was when i did it anyway - white boards where hi-tech back then)

 

[Barruel]

happy chalking! (well it was when i did it anyway - white boards where hi-tech back then)

This will indeed make chalking a bit easier. Blackboards are still green and I hate the white ones.

What about the holidays? Have you took a look into it?

 

[diddi]

oh, i have thought...

a bit anyway...

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, Hols(15) As Date
    Hols(1) = DateSerial(2010, 11, 1)
    Hols(2) = DateSerial(2010, 12, 24)
    Hols(3) = DateSerial(2010, 12, 25)
    Hols(4) = DateSerial(2011, 2, 7)
    Hols(5) = DateSerial(2011, 4, 9)
    ' etc
    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col
    For hrow = 1 To 15
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Cells(SRow, Col) = "Holiday"
        Next Col
    Next hrow
End Sub

 

[Barruel]

oh, i have thought...

a bit anyway...

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, Hols(15) As Date
    Hols(1) = DateSerial(2010, 11, 1)
    Hols(2) = DateSerial(2010, 12, 24)
    Hols(3) = DateSerial(2010, 12, 25)
    Hols(4) = DateSerial(2011, 2, 7)
    Hols(5) = DateSerial(2011, 4, 9)
    ' etc
    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col
    For hrow = 1 To 15
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Cells(SRow, Col) = "Holiday"
        Next Col
    Next hrow
End Sub

Ok. I seem to understand that the macro checks in the second for loop whether the date it is about to print is in the list of holidays or not, and if it is it prints "Holiday". I'd like it better to just skip the date?

Also, it will be a lot easier to have the list of holidays in a range of cells and just point the macro to that range of cells. Most of the holidays are a group of days that Excel can fill and this way I'll avoid the tedious process of entering them manually in the macro.

Can any of these things be done?

 

[diddi]

put your hols in column A starting at A1

Sub MakeDates()
    '   i have used 9 holiday days
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, Hols(9) As Date
    For Row = 1 To 9
        Hols(Row) = Cells(Row, 1)
    Next Row
    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col
    For hrow = 1 To 9
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Columns(Col).Delete
        Next Col
    Next hrow
End Sub

 

[Barruel]

put your hols in column A starting at A1

Sub MakeDates()
    '   i have used 9 holiday days
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, Hols(9) As Date
    For Row = 1 To 9
        Hols(Row) = Cells(Row, 1)
    Next Row
    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col
    For hrow = 1 To 9
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Columns(Col).Delete
        Next Col
    Next hrow
End Sub

I have to go now. Will test later and post feedback.

Thanks, diddi. You made my day.

 

[diddi]

no worries. its after midnight here, so i might call it as well.

 

[diddi]

a bit of housekeeping:

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, HDays as long

    ' find out how many holidays you have in col A

    Hdays=Range("A65536").End(xlUp).Row      For Row = 1 To hdays
        Hols(Row) = Cells(Row, 1)
    Next Row

    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col

    ' dump the holiday columns
    For hrow = 1 To hdays
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Columns(Col).Delete
        Next Col
    Next hrow
End Sub

 

[Barruel]

a bit of housekeeping:

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, HDays as long

    ' find out how many holidays you have in col A

    Hdays=Range("A65536").End(xlUp).Row      For Row = 1 To hdays
        Hols(Row) = Cells(Row, 1)
    Next Row

    Start = DateSerial(2010, 9, 1)
    SRow = Selection.Row
    SCol = Selection.Column
    Cells(SRow, SCol) = Start
    Cells(SRow, SCol + 1) = Start + 1
    Cells(SRow, SCol + 2) = Start + 2
    For Col = SCol + 3 To SCol + 200 Step 3
        Cells(SRow, Col) = Cells(SRow, Col - 3) + 7
        Cells(SRow, Col + 1) = Cells(SRow, Col - 2) + 7
        Cells(SRow, Col + 2) = Cells(SRow, Col - 1) + 7
    Next Col

    ' dump the holiday columns
    For hrow = 1 To hdays
        For Col = SCol + 3 To SCol + 200
            If Hols(hrow) = Cells(SRow, Col) Then Columns(Col).Delete
        Next Col
    Next hrow
End Sub

This gives a syntax error in the line "Hdays=Range("A65536").End(xlUp).Row For Row = 1 To hdays".

I separated the line by placing "For Row = 1 To hdays" in a new line and now it complains about "undefined sub or function" on the line "Hols(Row) = Cells(Row, 1)".

BTW, I'm using Excel 2000, which is fairy old. Can this be the problem?