Reputation: 25
I'm trying to use the SEQUENCE formula in range.formula2 code line but getting a run-time error. Am I missing something here when using Dynamic Array Formulas?
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
Upvotes: 1
Views: 64
Reputation: 76000
Two things:
You probably want to expand the double quotes in the 2nd parameter of IF()
to """"
. If you don't you'll get a run-time error since the function itself does not accept an empty parameter (which is what you are doing if you don't double up the quotes again);
Why don't you use Weekday()
in conjunction with WeekdayName()
? This saves you all the hassle with Select Case
. Something like:
Range(WeekdayName(Weekday(startDate, 2), 1, 2)).Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"
Note: You are currently using an implicit Worksheet()
object, namely the currently active one. Be sure to getting accustomed to using explicit range objects in the future.
Upvotes: 2