Array formula with more than 255 characters via VBA

Vote count:

0

I am struggling with the Array Formula (via VBA). Basically in my function, I add a column to a 'table' and then provide an array formula to that column. My Array formula is more than 255 characters. I found a solution here Entering Long Array Formulas in VBA, but it is not working for me. Below is my code. I will really appreciate if someone can help. Thanks.

Set myNewColDC = Worksheets("DE").ListObjects("Table1").ListColumns.Add(Position:=(Worksheets("DE").ListObjects("Table1").ListColumns.Count - 2))
myNewColDC.Name = ThemeCleanedColHeader & " DC"

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String
theFormulaPart1 = "=AND(NOT(OR(SUMIF(Lst_Indic_" & ThemeCleaned & ",Lst_Indic_" & ThemeCleaned & ",OFFSET(I12,0,0,1,nbcol" & ThemeCleaned & "))>1)),AND(IF(COUNTIFS(OFFSET(I12,0,0,1,nbcol" & ThemeCleaned & "),""?"",Lst_Indic_" & ThemeCleaned & "," & _
"X_X_X()"

theFormulaPart2 = "Lst_Indic_" & ThemeCleaned & ")=0,COUNTIF(Lst_Indic_" & ThemeCleaned & ",Lst_Indic_" & ThemeCleaned & "),COUNTIFS(OFFSET(I12,0,0,1,nbcol" & ThemeCleaned & "),""?""," & _
"Y_Y_Y()"

theFormulaPart3 = "Lst_Indic_" & ThemeCleaned & ",Lst_Indic_" & ThemeCleaned & "))=COUNTIF(Lst_Indic_" & ThemeCleaned & ",Lst_Indic_" & ThemeCleaned & ")))*1"

With ActiveSheet.Range("as12:As123")
 .Formula = theFormulaPart1
 .Replace What:="X_X_X()", _
             Replacement:=theFormulaPart2, _
             LookAt:=xlPart, _
             MatchCase:=True, _
             MatchByte:=False

.Replace What:="Y_Y_Y()", _
             Replacement:=theFormulaPart3, _
             LookAt:=xlPart
 End With

Malick

asked 2 mins ago

Malick

7

Array formula with more than 255 characters via VBA