Entering date of the day of the week in excel vba-CodePudding

I have created a time card worksheet for my employees to submit. Column A is the day name and I want column B to auto-populate the date of that day. Here is the module I have created...

Sub AutoDate()

    Dim NextSun As Date
    Dim NextMon As Date
    Dim NextTues As Date
    Dim NextWed As Date
    Dim NextThur As Date
    Dim NextFri As Date
    Dim NextSat As Date


    '*****Autopopulate the dates of the day of the week*****

    NextSun = ((Weekday(Date, vbSunday)))
    Range("C26") = NextSun
    NextSun = ((Weekday(Date, vbMonday)))
    Range("C27") = NextMon
    NextSun = ((Weekday(Date, vbTuesday)))
    Range("C28") = NextTues
    NextSun = ((Weekday(Date, vbWednesday)))
    Range("C29") = NextWed
    NextSun = ((Weekday(Date, vbThursday)))
    Range("C30") = NextThur
    NextSun = ((Weekday(Date, vbFriday)))
    Range("C31") = NextFri
    NextSat = ((Weekday(Date, vbSaturday)))
    Range("C32") = NextSat

End Sub

Thank you in advance for your assistance in this matter.

CodePudding user response：

So I assume you Always start with Sunday in B26 and Monday in B27 and so on, and want to just print the dates for the next week – hence the variable name – in column C?

Weekday() will only return a number, not a date, so that alone will not likely do what you want. However, you can add it onto the existing date.

Sub AutoDate()

    Dim NextDay As Range
    Dim i As Long

    Set NextDay = Range("C26")
    For i = 0 To 6
        NextDay.Offset(i) = Date   (8 - Weekday(Date)   i)
    Next i

End Sub

Change that 8 to a 1 if you want the current week.

CodePudding user response：

This will fill seven rows of column B and C with dates of the current week:

Sub AutoDate()

    Dim NameRange   As Range
    Dim DateRange   As Range
    
    Dim WeekdayDate As Date
    Dim WeekdayName As String
    Dim StartDate   As Date
    Dim Offset      As Long

    Set NameRange = Range("C26")
    Set DateRange = Range("B26")
    
    StartDate = DateAdd("d", 1 - Weekday(Date, vbSunday), Date)
    For Offset = 0 To 6
        WeekdayDate = DateAdd("d", Offset, StartDate)
        WeekdayName = Format(WeekdayDate, "dddd")
        DateRange.Offset(Offset) = WeekdayDate
        NameRange.Offset(Offset) = WeekdayName
    Next

End Sub