VBA error, Unable to set the FormulaArray property of the Range class

[anto2696]

Hello, I have tried to apply this formula from a macro but it indicates an error due to the number of characters, I have tried to separate it but it does not work well. Please request your help. Thanks a lot

Fórmula:

Sub Sinumbral()

Range("C6").Select
Selection.FormulaArray = _
"= + IF (Macro! R [43] C [-1] =" "N / A" ", SUM (IFERROR (1 / COUNTIF ('Prueba 1'! RC [-2]: R [1048570] C [ -2], 'Prueba 1'! RC [-2]: R [1048570] C [-2]), 0)), "& Chr (10) y" IF (Macro! R [43] C [-1] ] = "" Mayor a "", SUM (IFERROR (1 / COUNTIF ('Info adic Prueba 1'! RC [-2]: R [1048570] C [-2], 'Info adic Prueba 1'! RC [- 2]: R [1048570] C [-2]), 0)), "" "")) "

End Sub

To be able to separate it I have done the following:


Sub

Dim formulaPart As String
formulaPart = " SUM(IFERROR(1/COUNTIF('Prueba 1'!RC[-2]:R[1048570]C[-2], " & _
" 'Prueba 1'!RC[-2]:R[1048570]C[-2]),0))," & Chr(10) & "IF(Macro!R[43]C[-1]=""Mayor a "", " & _
" SUM(IFERROR(1/COUNTIF('Info adic Prueba 1'!RC[-2]:R[1048570]C[-2],'Info adic Prueba 1'!RC[-2]:R[1048570]C[-2]),0)),""""))"

With Range("C6")
.FormulaArray = "=+IF(Macro!R[43]C[-1]=""N/A"", X_X_X)"
.Replace "X_X_X", formulaPart, xlPart
End With

End Sub

 

[Domenic]

Try using the A1 reference style for your replacement string in formulaPart, instead of the R1C1 reference style...