CODEWITHSUNDEEP
arraysexcelvbaexcel-formuladynamic
markkeith
markkeith

Reputation: 25

Run-time error when using dynamic array formula SEQUENCE

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

Answers (1)

JvdV
JvdV

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

Related Questions