1004 Array too long

[manthony]

Hi, I am hoping someone will be able to help me break these four arrays in bit sizes pieces so my macro will run. The code is below (in R1C1 and nonR1C1 language).

The arrays are using whatever sheet name I am on to search another sheet (Vacation Time) for an employees amount of vacation time used.

Arrays 1 and 3 are finding the dates the vacation time was used on the 'Vacation Time' Sheet (always in column A of the Vacation Time Sheet).

Arrays 2 and 4 are finding the column that corresponds with the employee (based off the name of the current sheet, i.e.: Sheet called 'Steve' will find the Steve column on the Vacation Time sheet) and then returns the hours.

The formulas work very well, I am just getting the 1004 unable to set the formula array property range class because the arrays are over 255 characters. Any ideas? I will be forever grateful for the help.


'Start of the Macro with Count formula to figure out how many vacation entries on the Vacation Time sheet there are'
Range("A2").Select
ActiveCell.FormulaR1C1 = _
"=COUNT((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R[1],0)-1),10000,1)))"
Range("A4").Select

Array #1


Range("A6").Select
Selection.FormulaArray = _
"=IF(ROWS(R6C1:RC)>R2C1,"""",INDEX('Vacation Time'!R4C1:R1002C1,SMALL(IF(ISNUMBER(OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)),ROW('Vacation Time'!R4C1:R1002C1)-ROW('Vacation Time'!R4C1)+1),ROWS(R6C1:RC))))"

Array #2

Range("B6").Select
Selection.FormulaArray = _
"=IFERROR(INDEX((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)),SMALL(IF(ISNUMBER((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1))),ROW((OFFSET('Vacation Time'!R2" & _
"C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)))-ROW((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),1,1)))+1),ROWS(RC2:R6C))),"""")" & _
""

Array #3


Range("A7").Select
Selection.FormulaArray = _
"=IF(OR(R[-1]C=""Total"",R[-1]C=""""),"""",IF(COUNT(R6C1:R[-1]C)=(R2C1-1),""Total"",IF(ROWS(R6C1:RC)>R2C1,"""",INDEX('Vacation Time'!R4C1:R1002C1,SMALL(IF(ISNUMBER(OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)),ROW('Vacation Time'!R4C1:R1002C1)-ROW('Vacation Time'!" & _
"R4C1)+1),ROWS(R6C1:RC))))))" & _
""
Selection.AutoFill Destination:=Range("A7:A350")
Range("A7:A350").Select
Range("B7").Select

Array #4

Selection.FormulaArray = _
"=IFERROR(INDEX((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)),SMALL(IF(ISNUMBER((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1))),ROW((OFFSET('Vacation Time'!R2" & _
"C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),10000,1)))-ROW((OFFSET('Vacation Time'!R2C1,2,(MATCH(PROPER(MID(CELL(""filename"",R1C1),FIND(""]"",CELL(""filename"",R1C1))+1,256)),'Vacation Time'!R3,0)-1),1,1)))+1),ROWS(R6C2:RC))),"""")" & _
""
Selection.AutoFill Destination:=Range("B7:B350")
Range("B7:B350").Select
Range("A1").Select



Here are the four arrays in nonR1C1 langauge

Array #1
=IF(ROWS($A$6:A6)>$A$2,"",INDEX('Vacation Time'!$A$4:$A$1002,SMALL(IF(ISNUMBER(OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),10000,1)),ROW('Vacation Time'!$A$4:$A$1002)-ROW('Vacation Time'!$A$4)+1),ROWS($A$6:A6))))

Array #2
=IF(ROWS($D$6:D6)>$D$2,"",INDEX('Sick Time'!$A$4:$A$1002,SMALL(IF(ISNUMBER(OFFSET('Sick Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Sick Time'!$3:$3,0)-1),10000,1)),ROW('Sick Time'!$A$4:$A$1002)-ROW('Sick Time'!$A$4)+1),ROWS($D$6:D6))))

Array #3
=IF(OR(A6="Total",A6=""),"",IF(COUNT($A$6:A6)=($A$2-1),"Total",IF(ROWS($A$6:A7)>$A$2,"",INDEX('Vacation Time'!$A$4:$A$1002,SMALL(IF(ISNUMBER(OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),10000,1)),ROW('Vacation Time'!$A$4:$A$1002)-ROW('Vacation Time'!$A$4)+1),ROWS($A$6:A7))))))

Array #4

=IFERROR(INDEX((OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),10000,1)),SMALL(IF(ISNUMBER((OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),10000,1))),ROW((OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),10000,1)))-ROW((OFFSET('Vacation Time'!$A$2,2,(MATCH(PROPER(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)),'Vacation Time'!$3:$3,0)-1),1,1)))+1),ROWS($B$6:B7))),"")