.FormulaArray causes Run Time Error 1004, Unable to set the FormulaArray property

Hello,

In the Excel file, the formula array is

{=SUM(IF($A152='\\network drive full path\[Excel File Name.xlsx]Sheet Name'!$AG:$AG,'\\network drive full path\[Excel File Name.xlsx]Sheet Name'!Q:Q,0))}

.

The macro that I've created is basically assigning the range, i.e $AG:$AG and/or Q:Q to $AG1:$AG5000 and/or Q1:Q5000 respectively.

Below is my macro and when check the result in the 'Immediate' Window, it's correct.

I even went and manually use the result from the 'Immediate' Window and paste it to the formula bar and Ctr + Shift + Enter and return no error.

Result from the 'Immediate' Window.

=SUM(IF($A152='\\network drive full path\[Excel File Name.xlsx]Sheet Name'!$AG1:$AG5000,'\\network drive full path\[Excel File Name.xlsx]Sheet Name'!Q1:Q5000,0))

But for some reason, VBA just throw me an error.

May I know what's wrong with it.

formulaText = currWS.Range("I" & rowToLoop).Formula

SumIfFormulaToKeep = Mid(formulaText, InStr(formulaText, "SUM(IF"), InStr(formulaText, "!") - InStr(formulaText, "SUM(IF") + 1)    'Result in SUM(IF($A152='\\network drive full path\[Excel File Name.xlsx]Sheet Name'!
SumIfFormulaChar = Mid(formulaText, InStr(formulaText, "!") + 2, InStr(formulaText, ":") - InStr(formulaText, "!") - 2)                     'Result in AG
                            
SumIfTrueFormulaToKeep = Mid(formulaText, InStrRev(formulaText, ",'\\baxter") + 1, InStrRev(formulaText, "!") - InStrRev(formulaText, ",'\\network"))   'Result in '\\network drive full path\[Excel File Name.xlsx]Sheet Name'!
SumIfTrueFormulaChar = Mid(formulaText, InStrRev(formulaText, "!") + 1, InStrRev(formulaText, ":") - InStrRev(formulaText, "!") - 1)                             'Result in Q
                            
correctedSumIfFormula = "=" & SumIfFormulaToKeep & "$" & SumIfFormulaChar & "1:$" & SumIfFormulaChar & "5000," & SumIfTrueFormulaToKeep & SumIfTrueFormulaChar & "1:" & SumIfTrueFormulaChar & "5000,0))"   'Result in =SUM(IF($A152='\\network drive full path\[Excel File Name.xlsx]Sheet Name'!$AG1:$AG5000,'\\network drive full path\[Excel File Name.xlsx]Sheet Name'!Q1:Q5000,0))
                            
currWS.Range("I" & rowToLoop).FormulaArray = correctedSumIfFormula   <--- This is the error that's highlighted