VBA Array Formula

[CoolBusiness]

I have a macro that is used to apply formulas to a table. The macro runs fine until I try to set an array formula. The formula is in a criteria worksheet and is a named range.

The named Range's value is dimmed as FormCW, for the formula to place in column CW. The formula value is equal to:

=IFERROR(INDEX(Last_SRM_AsOfDate,MATCH($G2&"YES",Last_SRM_UIC&Last_SRM_Most_Recent_As_Of?,0)),"")

where each of the named ranges in the formula are in the same table with equal starting and ending rows. If I enter this formula manually in the column and hit CTR-SHIFT-ENTER it fills the column fine as an array. It just won't do it in the macro.

Here is the full macro. I'll highlight in red the problem point. This is my first post so hopefully I'm posting correctly.

Sub Apply_Formulas_to_SRM()
'
' Apply_Formulas_to_SRM Macro
' Applies formulas to the SRM tab and then after calculation replaces them with their calculated values
'


'
Application.ScreenUpdating = False
Application.EnableCancelKey = xlInterrupt


Dim School, LastRow, ApplyValues
Dim FormC, FormE, FormAO, FormBP, FormBQ, FormBU, FormBV, FormBW, FormBY, FormCA, _
    FormCF, FormCG, FormCH, FormCJ, FormCK, FormCM, FormCN, FormCO, FormCP, FormCQ, _
    FormCR, FormCS, FormCT, FormCU, FormCV, FormCW, FormCX As Variant, FormCY
Dim LastAsOfDate As Range, LastSRMUIC As Range, LastSRMRecentAsOf As Range
Set LastAsOfDate = Range("Last_SRM_AsOfDate")
Set LastSRMUIC = Range("Last_SRM_UIC")
Set LastSRMRecentAsOf = Range("Last_SRM_Most_Recent_As_Of?")
School = Range("SRM_School").Value
ApplyValues = Range("SRM_Apply_Values?").Value
FormC = "=" & Range("Formula_SRM_Trim_ENROLLMENT_DATE").Value
FormE = "=" & Range("Formula_SRM_Trim_GENERALED_FTE").Value
FormAO = "=" & Range("Formula_SRM_Trim_LEP_INSTRUCTIONAL_PROGRAM").Value
FormBP = "=" & Range("Formula_SRM_Trim_AS_OF_DATE").Value
FormBQ = "=" & Range("Formula_SRM_Trim_IEP_DATE").Value
FormBU = "=" & Range("Formula_SRM_Trim_PRIMARY_DISABILITY").Value
FormBV = "=" & Range("Formula_SRM_Trim_PRIMARY_EDUCATIONAL_SETTING").Value
FormBW = "=" & Range("Formula_SRM_Trim_PROGRAM_SERVICE_CODE").Value
FormBY = "=" & Range("Formula_SRM_Trim_SECTION52_FTE").Value
FormCA = "=" & Range("Formula_SRM_Trim_SPECED_EXIT_DATE").Value
FormCF = "=" & Range("Formula_SRM_Trim_DAYS_ATTENDED").Value
FormCG = "=" & Range("Formula_SRM_Trim_TOTAL_POSSIBLE_ATTENDENCE").Value
FormCH = "=" & Range("Formula_SRM_Trim_SECTION25").Value
FormCJ = "=" & Range("Formula_SRM_Trim_Is_SPED?").Value
FormCK = "=" & Range("Formula_SRM_Trim_Is_Weekend?").Value
FormCM = "=" & Range("Formula_SRM_Trim_State_Enroll_Date").Value
FormCN = "=" & Range("Formula_SRM_Trim_State_Exit_Date").Value
FormCO = "=" & Range("Formula_SRM_Trim_Date_after_Last_Attended").Value
FormCP = "=" & Range("Formula_SRM_Trim_SRM_Exit_Date").Value
FormCQ = "=" & Range("Formula_SRM_Trim_GC_Exit_Date").Value
FormCR = "=" & Range("Formula_SRM_Trim_School_Enrollment_Date").Value
FormCS = "=" & Range("Formula_SRM_Trim_Reported_in_SRM").Value
FormCT = "=" & Range("Formula_SRM_Trim_Section_25_Reported?").Value
FormCU = "=" & Range("Formula_SRM_Trim_Reported_in_GC").Value
FormCV = "=" & Range("Formula_SRM_Trim_Formula_Testing_Window_Enrollment_Warning").Value
FormCW = "=" & Range("Formula_SRM_Trim_Earliest_As_of_Date").Value
FormCX = "=" & Range("Formula_SRM_Trim_Last_SRM_AsOf").Value
FormCY = "=" & Range("Formula_SRM_Trim_Testing_Window_AsOf_Warning").Value


Sheets("" & School & " SRM").Select
Range("B1").Select
LastRow = Cells.SpecialCells(xlLastCell).Row
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row


Range("C2:C" & LastRow).Formula = FormC
Range("E2:E" & LastRow).Formula = FormE
Range("AO2:AO" & LastRow).Formula = FormAO
Range("BP2:BP" & LastRow).Formula = FormBP
Range("BQ2:BQ" & LastRow).Formula = FormBQ
Range("BU2:BU" & LastRow).Formula = FormBU
Range("BV2:BV" & LastRow).Formula = FormBV
Range("BW2:BW" & LastRow).Formula = FormBW
Range("BY2:BY" & LastRow).Formula = FormBY
Range("CA2:CA" & LastRow).Formula = FormCA
Range("CF2:CF" & LastRow).Formula = FormCF
Range("CG2:CG" & LastRow).Formula = FormCG
Range("CH2:CH" & LastRow).Formula = FormCH
Range("CJ2:CJ" & LastRow).Formula = FormCJ
Range("CK2:CK" & LastRow).Formula = FormCK
Range("CM2:CM" & LastRow).Formula = FormCM
Range("CN2:CN" & LastRow).Formula = FormCN
Range("CO2:CO" & LastRow).Formula = FormCO
Range("CP2:CP" & LastRow).Formula = FormCP
Range("CQ2:CQ" & LastRow).Formula = FormCQ
Range("CR2:CR" & LastRow).Formula = FormCR
Range("CS2:CS" & LastRow).Formula = FormCS
Range("CT2:CT" & LastRow).Formula = FormCT
Range("CU2:CU" & LastRow).Formula = FormCU
Range("CV2:CV" & LastRow).Formula = FormCV
Range("CW2:CW" & LastRow).Formula = FormCW
Range("CX2:CX" & LastRow).Formula.Array = FormCX        (this is where it errors out)
Range("CY2:CY" & LastRow).Formula = FormCY


Range("SRM_Trim_AsOf_Set?").ClearContents ' removes any "Yes" answers




If ApplyValues = "Yes" Then GoTo ChangetoValues Else GoTo EndMacro


ChangetoValues:
Range("A2:CY" & LastRow).Copy
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
GoTo EndMacro


EndMacro:
Range("A1").Select
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
Sheets("Tools").Select
End Sub

 

[RoryA]

Welcome to the forum.

You need to use .FormulaArray and not .Formula.Array

Also note that array formulas are limited to 255 characters when input by VBA, though there are workarounds.

 

[CoolBusiness]

Thanks. I made that change and I get this error:
Run-time error '1004':
Unable to to set the Formula Array property of the Range class

 

[Marcelo Branco]

In VBA to put an array formula in a range is a little tricky. You cannot simply use
Range("CX2:CX" & lastRow).FormulaArray = ...

Maybe this article can be helpful
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

look for topic
3.2 Setting A Multi-Cell Array Formula

Hope this helps

M.

 

[RoryA]

What is the actual value of FormCX when the error occurs?

 

[RoryA]

In VBA to put an array formula in a range is a little tricky. You cannot simply use
Range("CX2:CX" & lastRow).FormulaArray = ...

To clarify, you can do that if the array-formula would be array entered into multiple cells at once if you did it in the Excel UI; if you would normally array enter into one cell and then fill down, you need to do the same in code too.

 

[CoolBusiness]

Thank you. I'll look at that article. Where I've looked at examples I've noticed there are no named ranges in the examples I've looked at.

 

[CoolBusiness]

Well, it won't set the first cell, but the value when calculated in the first cell is "" blank.

 

[CoolBusiness]

In a variation of the code, I tried that exact thing, I think, to set the formula in the first cell only, and then after copy or fill it from row 3 down to the last cell. It failed in trying to set even one cell.

 

[Marcelo Branco]

To clarify, you can do that if the array-formula would be array entered into multiple cells at once if you did it in the Excel UI; if you would normally array enter into one cell and then fill down, you need to do the same in code too.

Thanks for clarifying. That was what I should have said.

M.