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
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