strange formula array issue
 

Hello. I have a program which is putting a formula array in a cell.
It uses a subtotal function to only count those cells which have not
been auto filtered. I got it to work perfectly using the following.

Dim Rng As Range
Dim RngStat As Range
Dim RndData As Range
Dim RngPri As Range
Dim RngSco as Range
Dim StrSubtot As String
Const YAT As Integer = 30
Const YWL As Integer = 90

StrSubtot = "SUBTOTAL(3,OFFSET(" & RngData.Cells(1, 1).Address &
",ROW(" & RngData.Columns(1).Address & ")-ROW(" & RngData.Cells(1,
1).Address & "),0))*"

Rng.Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address
& "=1)*(" & RngStat.Address & "=""YWL"")*(" & RngStat.Offset(0,
1).Address(columnabsolute:=False) & "=" & YWL & "))/SUM(" & StrSubtot
& "(" & RngPri.Address & "=1)*(" & RngStat.Address & "=""YWL""))"

but I get a runtime 1004, Unable to set the FormulaArray property of
the Range class when the last line becomes

Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address &
"=1)*(" & RngSco.Address & "<""R"")*(" & RngStat.Address &
"=""YWL"")*(" & RngStat.Offset(0, 1).Address(columnabsolute:=False) &
"=" & YWL & "))/SUM(" & StrSubtot & "(" & RngPri.Address & "=1)*(" &
RngSco.Address & "<""R"")*(" & RngStat.Address & "=""YWL""))"

where RngSco.Address & "<""R"" is added. Is there a problem
concatenating strings with < or does someone else have a suggestion
as to why this will not work. I copied and pasted that actual string
that this line produces into the cell and the result was correct but
it will not run in the macro. Help, please.

H

strange formula array issue
 

The resulting formula is probably longer than 255 characters in R1C1 format
(limit for inserting array formula using VBA).

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"redtwotwo" wrote in message
...
Hello. I have a program which is putting a formula array in a cell.
It uses a subtotal function to only count those cells which have not
been auto filtered. I got it to work perfectly using the following.

Dim Rng As Range
Dim RngStat As Range
Dim RndData As Range
Dim RngPri As Range
Dim RngSco as Range
Dim StrSubtot As String
Const YAT As Integer = 30
Const YWL As Integer = 90

StrSubtot = "SUBTOTAL(3,OFFSET(" & RngData.Cells(1, 1).Address &
",ROW(" & RngData.Columns(1).Address & ")-ROW(" & RngData.Cells(1,
1).Address & "),0))*"

Rng.Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address
& "=1)*(" & RngStat.Address & "=""YWL"")*(" & RngStat.Offset(0,
1).Address(columnabsolute:=False) & "=" & YWL & "))/SUM(" & StrSubtot
& "(" & RngPri.Address & "=1)*(" & RngStat.Address & "=""YWL""))"

but I get a runtime 1004, Unable to set the FormulaArray property of
the Range class when the last line becomes

Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address &
"=1)*(" & RngSco.Address & "<""R"")*(" & RngStat.Address &
"=""YWL"")*(" & RngStat.Offset(0, 1).Address(columnabsolute:=False) &
"=" & YWL & "))/SUM(" & StrSubtot & "(" & RngPri.Address & "=1)*(" &
RngSco.Address & "<""R"")*(" & RngStat.Address & "=""YWL""))"

where RngSco.Address & "<""R"" is added. Is there a problem
concatenating strings with < or does someone else have a suggestion
as to why this will not work. I copied and pasted that actual string
that this line produces into the cell and the result was correct but
it will not run in the macro. Help, please.

H

strange formula array issue
 

You can try shortening your sheet names or use Range.replace to bypass this
problem:
see
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

for an example

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Charles Williams" wrote in message
...
The resulting formula is probably longer than 255 characters in R1C1
format (limit for inserting array formula using VBA).

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"redtwotwo" wrote in message
...
Hello. I have a program which is putting a formula array in a cell.
It uses a subtotal function to only count those cells which have not
been auto filtered. I got it to work perfectly using the following.

Dim Rng As Range
Dim RngStat As Range
Dim RndData As Range
Dim RngPri As Range
Dim RngSco as Range
Dim StrSubtot As String
Const YAT As Integer = 30
Const YWL As Integer = 90

StrSubtot = "SUBTOTAL(3,OFFSET(" & RngData.Cells(1, 1).Address &
",ROW(" & RngData.Columns(1).Address & ")-ROW(" & RngData.Cells(1,
1).Address & "),0))*"

Rng.Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address
& "=1)*(" & RngStat.Address & "=""YWL"")*(" & RngStat.Offset(0,
1).Address(columnabsolute:=False) & "=" & YWL & "))/SUM(" & StrSubtot
& "(" & RngPri.Address & "=1)*(" & RngStat.Address & "=""YWL""))"

but I get a runtime 1004, Unable to set the FormulaArray property of
the Range class when the last line becomes

Cells(1).FormulaArray = "=SUM(" & StrSubtot & "(" & RngPri.Address &
"=1)*(" & RngSco.Address & "<""R"")*(" & RngStat.Address &
"=""YWL"")*(" & RngStat.Offset(0, 1).Address(columnabsolute:=False) &
"=" & YWL & "))/SUM(" & StrSubtot & "(" & RngPri.Address & "=1)*(" &
RngSco.Address & "<""R"")*(" & RngStat.Address & "=""YWL""))"

where RngSco.Address & "<""R"" is added. Is there a problem
concatenating strings with < or does someone else have a suggestion
as to why this will not work. I copied and pasted that actual string
that this line produces into the cell and the result was correct but
it will not run in the macro. Help, please.

H