How to enter array formula with VBA

[R. Choate]

I need to programmatically enter an array formula into a cell after the ranges involved in the formula are determined. I also do not
want to use the R1C1 format. My "base" formula is =SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and the
M2:BC2 will change each time I place this formula into a cell. I know that the general manner of entering an array formula is to use
syntax which begins with the word "Evaluate". Can somebody help me on this one?

 

[Bob Phillips]

How do you determine the changed values.

The general format for array formulae is

activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1,3)=""Alt"",M2:BC2,0))"

--

HTH

RP
(remove nothere from the email address if mailing direct)

I need to programmatically enter an array formula into a cell after the

ranges involved in the formula are determined. I also do not

want to use the R1C1 format. My "base" formula is

=SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and
the

M2:BC2 will change each time I place this formula into a cell. I know that

the general manner of entering an array formula is to use

 

[R. Choate]

I determine the changed values by counting the columns to the right from M1 and then making adjustments to convert that into the
proper cell address. Once this is done, I will copy the array formula down until the cell to the right is blank.

--
RMC,CPA


How do you determine the changed values.

The general format for array formulae is

activecell.formulaarray="=SUM(IF(RIGHT($M$1:$BC$1,3)=""Alt"",M2:BC2,0))"

--

HTH

RP
(remove nothere from the email address if mailing direct)

I need to programmatically enter an array formula into a cell after the

ranges involved in the formula are determined. I also do not

want to use the R1C1 format. My "base" formula is

=SUM(IF(RIGHT($M$1:$BC$1,3)="Alt",M2:BC2,0)). However, the $M$1:$BC$1 and
the

M2:BC2 will change each time I place this formula into a cell. I know that

the general manner of entering an array formula is to use

 

[Tushar Mehta]

Using XL's macro recorder and leveraging its object model can lead to
wonders.

First, use the macro recorder to enter what you call the base formula
into a cell. The result:

Selection.FormulaArray = _
"=SUM(IF(RIGHT(R1C13:R1C55,3)=""Alt"",R[-1]C[3]:R[-1]C[45],0))"

Next, we replace the specific cell references with their generalized
counterparts.

Dim aRng As Range
Set aRng = Range(Range("m1"), Range("m1").End(xlToRight))
Selection.FormulaArray = _
"=SUM(IF(RIGHT(" & aRng.Address _
& ",3)=""Alt""," _
& aRng.Offset(1).Address(False, False) & ",0))"

Finally, suppose the formula should be in column L for all rows that
contain information in M:whatever. Then, we replace the last line
above with:

Range("L2").FormulaArray = _
"=SUM(IF(RIGHT(" & aRng.Address _
& ",3)=""Alt""," _
& aRng.Offset(1).Address(False, False) & ",0))"
Range(Range("m2"), Range("m2").End(xlDown)).Offset(, -1).FillDown

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[R. Choate]

Thank you for your help. I definitely agree with the wonders of using the macro recorder in many situations. At the same time, it
can occasionally yield some ridiculous code that is worthless. Everything is working now.
--
RMC,CPA


Using XL's macro recorder and leveraging its object model can lead to
wonders.

First, use the macro recorder to enter what you call the base formula
into a cell. The result:

Selection.FormulaArray = _
"=SUM(IF(RIGHT(R1C13:R1C55,3)=""Alt"",R[-1]C[3]:R[-1]C[45],0))"

Next, we replace the specific cell references with their generalized
counterparts.

Dim aRng As Range
Set aRng = Range(Range("m1"), Range("m1").End(xlToRight))
Selection.FormulaArray = _
"=SUM(IF(RIGHT(" & aRng.Address _
& ",3)=""Alt""," _
& aRng.Offset(1).Address(False, False) & ",0))"

Finally, suppose the formula should be in column L for all rows that
contain information in M:whatever. Then, we replace the last line
above with:

Range("L2").FormulaArray = _
"=SUM(IF(RIGHT(" & aRng.Address _
& ",3)=""Alt""," _
& aRng.Offset(1).Address(False, False) & ",0))"
Range(Range("m2"), Range("m2").End(xlDown)).Offset(, -1).FillDown

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions