Shorten R1C1 formula for array

[john316swan]

Is there a trick to get around the 255 R1C1 character limit? I am trying to convert a formula to an array but get a run-time error '13' type mismatch error. I then tried to use named ranges to breakup the formula into pieces which shortened the formula...it worked, but it took WAY too long.

Here is a snippet of what I am trying to do:

Sub formulaLoad()

Dim id as Range
Dim lr as long

lr = [RTA]
Set id = Worksheets("Ready").Range("A2:A" & lr)
'There are more ranges

With id
    .Formula = "=INDEX('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!C1,SMALL(IF('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!C18=""YES"",ROW('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!C18)-ROW(INDEX('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!C18,1,1))+1),ROW(R[-1]C)))"
    .FormulaArray = .FormulaR1C1
End With

'There is more code but it works just fine

End Sub

Am I going about this the wrong way? Any suggestions? Thank you for your help!

 

[mikerickson]

One quick way would be to use Named ranges.

 

[john316swan]

Thanks Mike - i did, but the code took way too long

 

[john316swan]

I ended up using replace & fill down:

arrayPart1 = "=INDEX('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!$A:$A,SMALL(IF('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!$R:$R=""YES"",ROW(A1),ROW(A2)),1))"
arrayPart2 = "ROW('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!$R:$R)-ROW(INDEX('M:\Financial Aid\ELECTRONIC FILES\SPS\[19-20 SPS Student Tracking.xlsm]Desiree'!$R:$R,1,1))+1),ROW(A1)))"


'Logic
'The R1C1 formula array is limited to 255 characters so we have to work around the limit using a replace function
Application.Calculation = xlCalculationManual
With Worksheets("Ready").Range("A2")
    .FormulaArray = arrayPart1
    .Replace "ROW(A1),ROW(A2)),1))", arrayPart2
End With


'Now this doesn't work with r1c1, so we have to take the array formula in cell 1 and fill it down to the last row in column A
Range("A2").AutoFill Destination:=Range("A2:A" & lr)
Application.Calculation = xlCalculationAutomatic