CODEWITHSUNDEEP
arraysvbaexcel
William C.
William C.

Reputation: 212

Long Array VBA Issue

I'm trying to enter a long-array Formula into VBA that is >255 characters. I have followed past suggestions to dim both halves of the formula and merge them later. I am still having errors getting the array to function properly and was hoping someone could help review the code.

Here's the original code that exceeds the character limit I'm trying to get working:

Sub TestMacro()

Range("AZ7").Select
Selection.FormulaArray = _
    "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

End Sub

Here is my latest attempt to split the code in half following past advice: https://www.mrexcel.com/forum/excel-questions/853889-long-array-visual-basic-applications-issue.html

http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/

Sub LongArrayFormula()
 Dim theFormulaPart1 As String
 Dim theFormulaPart2 As String
 theFormulaPart1 = "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])""X_X_X)"")"

 theFormulaPart2 = "=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

 With ActiveSheet.Range("AZ7")
     .FormulaArray = theFormulaPart1
    .Replace """X_X_X)"")", theFormulaPart2

End With

Any help is appreciated, thanks.

Upvotes: 3

Views: 1387

Answers (2)

Nikolaos Polygenis
Nikolaos Polygenis

Reputation: 691

You can also try this(
please don't run it from the VBE, try to run it from sheets environment. Go to Developer-Macros-Your Macro -Run or Run it from a button or shortcut and it will work without problem):

Range("AZ7").Select
Selection.Formula = _
    "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],[@Diameter],[@[Year Installed (Coating)]])=CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88]),HCA!R26C[-36]:R13642C[-36]))"

SendKeys "{F2}"
SendKeys "^+{ENTER}"

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

You should keep your truncated formula syntactically correct. Try it like this:

theFormulaPart1 = "=SUM(IF(CONCATENATE(R3C3,[@Route],[@[Assumed Coating Type]],
[@Diameter],[@[Year Installed (Coating)]])=X_X_X,HCA!R26C[-36]:R13642C[-36]))"
'                                          ^^^^^
theFormulaPart2 = "CONCATENATE(HCA!R26C[86]:R13642C[86],HCA!R26C[-48]:R13642C[-48],HCA!R26C[87]:R13642C[87],HCA!R26C[-19]:R13642C[-19],HCA!R26C[88]:R13642C[88])"

With ActiveSheet.Range("AZ7")
  .FormulaArray = theFormulaPart1
  .Replace "X_X_X", theFormulaPart2
End With

Here I inserted X_X_X (could be anything else) in the place of some "closed expression" in the formula. That keeps the truncated formula correct from a syntax point of view, so the statement .FormulaArray = theFormulaPart1 can accept it. Replacement can then proceed in the second step.

Upvotes: 2

Related Questions