Run-time error when using dynamic array formula SEQUENCE

Question

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

JvdV · Accepted Answer · 2022-08-13 08:58:27Z

2

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.

answered Aug 13, 2022 at 8:58

JvdV

76.8k8 gold badges48 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

markkeith Over a year ago

Thank you so much, expanding the double quotes fixed the error. I will also try the WeekdayName() function.

markkeith Over a year ago

Hi @JvdV, to be honest I'm newby to Excel VBA. I tried your 2nd suggestion but it doesn't populate the named range cell corresponding to WeekdayName, result is always the same even I choose different date.

markkeith Over a year ago

It works perfectly now, I refer the named range starDate as range ("startDate")

Collectives™ on Stack Overflow

Run-time error when using dynamic array formula SEQUENCE

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related