Shorten R1C1 formula for array

john316swan

Board Regular
Joined
Oct 13, 2016
Messages
66
Office Version
  1. 2019
Platform
  1. Windows
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:

Code:
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!
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I ended up using replace & fill down:

Code:
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
 
Upvote 0
Solution

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top