Works for .Formula but not for .FormulaArray

Question

I have the following code which works absolutely fine when i use Activecell.Formula... but gives me a 1004 Unable to set the FormulaArray property of Range Class when i use Activecell.FormulaArray

     Sub CreateFiberLossReport()
    '
    ' CreateFiberFiles Macro
    '  - by Shikhar Parashar
TotalJoints = Range("F3").Value
For freqCtr = 1 To 3
freqSheet = Choose(freqCtr, "1550", "1625", "1310")
'fiberNum = "001"
    For n = 9 To 176
        For TJNum = 2 To TotalJoints + 1
            ColToSelect = Choose(TJNum, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
            Sheets(freqSheet).Select
            rangeValue = ColToSelect & n

            Range(rangeValue).Select
            initPath = "C:\Users\Deepak\Desktop\Fazilka ferozpur AT\FRZ-JLB\["

            formulaValue = "=VLOOKUP(MIN(IF(ABS('" & initPath & (n / 3 - 2) & " " & freqSheet & ".xls]" & (n / 3 - 2) & " " & freqSheet & "'!$C$17:$C$28-" & ColToSelect & "5*1000)=MIN(ABS('" & initPath & (n / 3 - 2) & " " & freqSheet & ".xls]" & (n / 3 - 2) & " " & freqSheet & "'!$C$17:$C$28-" & ColToSelect & "5*1000)),IF(ABS('" & initPath & (n / 3 - 2) & " " & freqSheet & ".xls]" & (n / 3 - 2) & " " & freqSheet & "'!$C$17:$C$28-" & ColToSelect & "5*1000)< 150,'" & initPath & (n / 3 - 2) & " " & freqSheet & ".xls]" & (n / 3 - 2) & " " & freqSheet & "'!$C$17:$C$28,))),'" & initPath & (n / 3 - 2) & " " & freqSheet & ".xls]" & (n / 3 - 2) & " " & freqSheet & "'!$C$17:$E$28,3,FALSE)"
            ActiveCell.Formula = formulaValue
        Next
    Next
Next
 End Sub

Answer 1

Your left square bracket is in the wrong position in initPath. It should be at the start.

This is bad practice. You should have the left square bracket in the formulaValue = statement rather than in the variable itself.

eg.

formulaValue = "=VLOOKUP(MIN(IF(ABS('[" & initPath & (n / 3 - 2) & " " .......