WeekdayName instead of Select Case

[markkeith]

Is it possible to simplify the Select Case method

Select Case Weekday(Range("startDate"), vbMonday)
    Case 1
     Range("week").ClearContents
     Range("mon").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 2
     Range("week").ClearContents
     Range("tue").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 3
     Range("week").ClearContents
     Range("wed").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 4
     Range("week").ClearContents
     Range("thu").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 5
     Range("week").ClearContents
     Range("fri").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 6
     Range("week").ClearContents
     Range("sat").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
    Case 7
     Range("week").ClearContents
     Range("sun").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
   End Select

with WeekdayName function?

Range("week").ClearContents
Range(WeekdayName(Weekday(StartDate, 2), 1, 2)).Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"

I'm getting the same result even I choose different date.

 

[Fluff]

Is "StartDate" meant to be the named range or a variable?

 

[markkeith]

Is "StartDate" meant to be the named range or a variable?

Hi @Fluff thank you for the follow-up question, Startdate is a named range where I input the date. There are also named range (mon-sun) that I want the formula to be populated once I type in the date.

 

[Fluff]

In that case you need to refer to the range like

Range(WeekdayName(Weekday(Range("StartDate"), 2), 1, 2)).Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"

 

[markkeith]

In that case you need to refer to the range like

Range(WeekdayName(Weekday(Range("StartDate"), 2), 1, 2)).Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"

Thank you so much @Fluff, it works perfectly now.

 

[Fluff]

Glad to help & thanks for the feedback.