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]

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

I modified the macro this way:

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, HDays As Long, Hols(65536) As Date

    ' 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

And now the macro executes successfully without any error.

As I have absolutely no idea of VBA, I'd like anyone knowledgeable to check if the macro is doing what it is meant to do or if my fix screwed it.

Did I destroyed or fixed diddi's macro?

TIA

 

[Barruel]

I modified the macro this way:

Sub MakeDates()
    Dim Start As Date, Col As Long, SRow As Long, SCol As Long, HDays As Long, Hols(65536) As Date

    ' 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

And now the macro executes successfully without any error.

As I have absolutely no idea of VBA, I'd like anyone knowledgeable to check if the macro is doing what it is meant to do or if my fix screwed it.

Did I destroyed or fixed diddi's macro?

TIA

Seems to be working. Are you there diddi? Can confirm?

 

[diddi]

yeh im am around now. visiting my mother in hospital for a few hours. she had a bit of a tumble recently.

 

[diddi]

your correction is suitable correct (is that a tautology?) anyway you have done well. do you want a commented version of this code?

 

[Barruel]

your correction is suitable correct (is that a tautology?) anyway you have done well. do you want a commented version of this code?

Hi, diddi. Hope your mom gets well soon.

I have subjects with 2, 3 and 4 days a week, so I had to modify the code accordingly and did it successfully, but just by lots of trial and error.

I'll be grateful if you can comment the code, so next year I can modify it again without such amount of trial and error. I'll probably forget how to do it in a couple of days.

Many thanks, diddi.