Unable to set FormulaArray property of the Range class Run time error 1004

[kyddrivers]

I am trying to write a script to create a sum if array formula in VBA. I created string variables for all of the different pieces of the puzzle and then assembled them in a final
Range("B6").Select
Selection.FormulaArray = NCHForm1 and I get the above error

When using Dr Google to help diagnose my issue, I came across the long array vba issue post. When I tried .FormulaArray = Formula1 .Replace "XXX", Formula2 I get the same run time error.

I recorded a macro and typed out the formula and life was good, until I inserted the full path of the file. Then the 1004 error popped up again.

The path alone is 111 characters. When I type out the formula completely it is 376 characters.

I am guessing the overall length is my issue, but I am not 100% sure. Is it something else?

Here is what the recorded macro spit out...
Range("B4").Select
Selection.FormulaArray = _
"=SUM(IF(4=MONTH('[Generic File Name.xlsb]Generic Tab Name'!R5C1:R16C1), '[Generic File Name.xlsb]Generic Tab Name'!R5C3:R16C3, 0))"

This causes the error to show up...
Range("B5").Select
Selection.FormulaArray = _
"=SUM(IF(4=MONTH(\\A\Really\Really\Really\Really\Long\Path\Name\'[Generic File Name.xlsb]Generic Tab Name'!R5C1:R16C1),\\A\Really\Really\Really\Really\Long\Path\Name\'[Generic File Name.xlsb]Generic Tab Name'!R5C3:R16C3, 0))"


Thanks for any insight you might be able to provide!

 

[DanteAmor]

Try this

  Dim f1 As String, longpath As String
  Application.DisplayAlerts = False
  longpath = "C:\Users\damor\Documents\Docs\Soporte Mr Excel\user files\"
  f1 = "'" & longpath & "[Generic File Name.xlsb]Generic Tab Name'"
  Range("B5").FormulaArray = "=SUM(IF(4=MONTH(x_x!R5C1:R16C1), x_x!R5C3:R16C3, 0))"
  Range("B5").Replace "x_x", f1, xlPart

See the details:
"'" & longpath & "[Generic File Name.xlsb]Generic Tab Name'"
"=SUM(IF(4=MONTH(x_x!R5C1:R16C1), x_x!R5C3:R16C3, 0))"

 

[kyddrivers]

Thanks Dante!

When I compare my puzzle pieces to yours, here are the differences I had...

• Single quotes in wrong place ...DUH
• I had the ! included in my path variable

What I am running into to is #ref error when the application.display alerts are off. The formula comes up with the x_x inside the formula and is not replacing it. When alerts are on, I get a open dialogue looking for a x_x file and if I cancel it, then it completes the replace.

 

[DanteAmor]

Maybe it's the excel version, in my tests, just as I put the code, the macro puts the formula and doesn't open the browser.?