VBA long array problem
I am trying to enter a long array formula in VBA that is> 255 characters. I followed previous suggestions to reduce both halves of the formula and combine them later. I still have errors to get the array to function as expected and was hoping someone could view the code.
Here's the source code that exceeds the character limit I'm trying to get:
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 at splitting the code in half of the following tips: 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.
source to share
You must remove the truncated formula syntactically correctly. 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 have inserted X_X_X
(maybe something else) in place of some "closed expression" in the formula. This preserves the correct shortened formula syntactically, so the operator .FormulaArray = theFormulaPart1
can accept it. The replacement can then continue in the second stage.
source to share
You can also try this (
please don't run it from VBE, try running it from sheet environment. Go to "Developer Macros" - "Macro-Run" or run it with a button or shortcut and it will work without issue):
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}"
source to share