Run Time Error 1004 - unable to set the formula array property of the range class

[KungFu Keyboard]

Hi there,

I have a Macro, that is giving the a run time error 1004. All the formulas work if not applied through VBA, but I guess the length is a bit much. Can you advise on how to shorten the below code please?

Sub SUBMITFLIGHT()


Sheets("FLIGHT SCHEDULE").Visible = True


    Range("C1:C22").Select
    Selection.Copy
    Range("E1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Range("E1:E22").Select
    Application.CutCopyMode = False
    Selection.Copy
        
    Sheets("FLIGHTS").Select
    Range("A1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True


    Range("AB1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.FormulaArray = "=TEXT(RC[-24],""DDDd"")"


    Range("W1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.FormulaArray = _
        "=INDEX('FLIGHT SCHEDULE'!C6,MATCH(1,('FLIGHT SCHEDULE'!C[-22]=FLIGHTS!RC[-21])*('FLIGHT SCHEDULE'!C[-21]<=FLIGHTS!RC[-19])*('FLIGHT SCHEDULE'!C[-20]>=FLIGHTS!RC[-19])*(INDEX('FLIGHT SCHEDULE'!C14:C20,,VLOOKUP(FLIGHTS!RC[5],{""Monday"",1;""Tuesday"",2;""Wednesday"",3;""Thursday"",4;""Friday"",5;""Saturday"",6;""Sunday"",7},2,FALSE))=FLIGHTS!RC[5]),0))"


    
    Range("X1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.FormulaArray = _
        "=INDEX('FLIGHT SCHEDULE'!C7,MATCH(1,('FLIGHT SCHEDULE'!C[-23]=FLIGHTS!RC[-22])*('FLIGHT SCHEDULE'!C[-22]<=FLIGHTS!RC[-20])*('FLIGHT SCHEDULE'!C[-21]>=FLIGHTS!RC[-20])*(INDEX('FLIGHT SCHEDULE'!C14:C20,,VLOOKUP(FLIGHTS!RC[4],{""Monday"",1;""Tuesday"",2;""Wednesday"",3;""Thursday"",4;""Friday"",5;""Saturday"",6;""Sunday"",7},2,FALSE))=FLIGHTS!RC[4]),0))"


    Range("Y1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-22],'FLIGHT SCHEDULE'!C[-9]:C[-7],3,FALSE)"
    
    Range("Z1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.FormulaArray = _
        "=INDEX('FLIGHT SCHEDULE'!C5,MATCH(1,('FLIGHT SCHEDULE'!C[-25]=FLIGHTS!RC[-24])*('FLIGHT SCHEDULE'!C[-24]<=FLIGHTS!RC[-22])*('FLIGHT SCHEDULE'!C[-23]>=FLIGHTS!RC[-22])*(INDEX('FLIGHT SCHEDULE'!C14:C20,,VLOOKUP(FLIGHTS!RC[2],{""Monday"",1;""Tuesday"",2;""Wednesday"",3;""Thursday"",4;""Friday"",5;""Saturday"",6;""Sunday"",7},2,FALSE))=FLIGHTS!RC[2]),0))"


    Range("AA1").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1).Select
    Selection.FormulaArray = _
        "=INDEX('FLIGHT SCHEDULE'!C9,MATCH(1,('FLIGHT SCHEDULE'!C[-26]=FLIGHTS!RC[-25])*('FLIGHT SCHEDULE'!C[-25]<=FLIGHTS!RC[-23])*('FLIGHT SCHEDULE'!C[-24]>=FLIGHTS!RC[-23])*(INDEX('FLIGHT SCHEDULE'!C14:C20,,VLOOKUP(FLIGHTS!RC[1],{""Monday"",1;""Tuesday"",2;""Wednesday"",3;""Thursday"",4;""Friday"",5;""Saturday"",6;""Sunday"",7},2,FALSE))=FLIGHTS!RC[1]),0))"


    Columns("D:D").Select
    Selection.NumberFormat = "dd/mm/yyyy;@"
    Columns("E:E").Select
    Selection.NumberFormat = "h:mm;@"
    Columns("W:X").Select
    Selection.NumberFormat = "h:mm;@"
    
    Sheets("FORM").Select
    Range("E1:E22").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("C2:C3").Select
    Selection.ClearContents
    Range("C5:C23").Select
    Selection.ClearContents


MsgBox "Thank You - Flight Report Saved"


Sheets("FLIGHT SCHEDULE").Visible = False
    
End Sub

 

[Vbagreenhorn1]

it has an char limit of 255 so the lenght is the problem for sure.

https://www.mrexcel.com/forum/excel-questions/687125-long-array-formulas-vba.html

maybe this link will help!

 

[KungFu Keyboard]

Thank you - problem solved!