CODEWITHSUNDEEP
arraysvbaexcel
Jacob Dorsey
Jacob Dorsey

Reputation: 197

Entering a long array formula in Excel VBA using the Replace method as a workaround

I am trying to avoid the 255 character limit in entering an array formula through VBA. I have looked online and found a promising solution of breaking the array into pieces and combining them again.

However, my .replace function is not replacing anything.

I was very careful to make sure that in each stage of the .replace that the resulting formula was logical.

Here is what I have so far:

Sub ArrayMacro()

Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim theFormulaPart3 As String


theFormulaPart1 = "=IF(SUM(IF(COUNTIFS(Ratting!A:A,'Site Matrix'!$B$1:$FA$1,Ratting!K:K,'Site Matrix'!A2)>=1,1,0))=1,1,1)"

theFormulaPart2 = "SUMIF(Ticks!E:E,'Site Matrix'!A2,Ticks!C:C)/IF(COUNTIFS(Ratting!N:N,TRUE,Ratting!K:K,'Site Matrix'!A2)>0,"",1))"

theFormulaPart3 = "COUNTIFS(Ratting!A:A,IF(COUNTIFS(Ratting!N:N,TRUE,Ratting!K:K,'Site Matrix'!A2)>0,"",INDEX(Ratting!A:A,MATCH('Site Matrix'!A2,Ratting!K:K,0))),Ratting!K:K,'Site Matrix'!A2)"


With Sheets("Site Matrix").Range("FB2")
    .FormulaArray = theFormulaPart1
    .Replace "1,1)", theFormulaPart2, xlPart
    .Replace "1))", theFormulaPart3, xlPart

End With

End Sub

The formula in the cell stays the same each time I run the macro. Stepping through the code throws no errors or hints as to what goes wrong. Just, nothing happens.

Upvotes: 1

Views: 3235

Answers (1)

BrakNicku
BrakNicku

Reputation: 5990

Both replacement strings are not defined right. You should use """"" instead of "" inside string constant.

Second problem - missing )) at the end of theFormulaPart3.

theFormulaPart2 = "SUMIF(Ticks!E:E,'Site Matrix'!A2,Ticks!C:C)/IF(COUNTIFS(Ratting!N:N,TRUE,Ratting!K:K,'Site Matrix'!A2)>0,"""",1))"  
theFormulaPart3 = "COUNTIFS(Ratting!A:A,IF(COUNTIFS(Ratting!N:N,TRUE,Ratting!K:K,'Site Matrix'!A2)>0,"""",INDEX(Ratting!A:A,MATCH('Site Matrix'!A2,Ratting!K:K,0))),Ratting!K:K,'Site Matrix'!A2)))"

Upvotes: 4

Related Questions