VBA range.formula2 error when using dynamic array formula SEQUENCE

[markkeith]

I'm trying to use the SEQUENCE formula in the code below but getting a run-time error.
Am I missing something in my code?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Application.ScreenUpdating = False

 If Not Intersect(Target, Range("startDate")) Is Nothing Then
  If IsDate(Range("startDate")) = True Then
   'Do nothing
   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
    
   Else
    MsgBox ("Please input a valid date.")
    Range("startDate").ClearContents
  End If
 End If
 
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Peter_SSs]

When entering a formula with vba you have to double-up all the quote marks within the formula. So they should be like ..

 Range("mon").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"

 

[markkeith]

When entering a formula with vba you have to double-up all the quote marks within the formula. So they should be like ..

 Range("mon").Formula2 = "=IF(ISBLANK(startDate),"""",SEQUENCE(1,days,startDate,1))"

Thank you so much Peter, it works like a charm.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.