How to enter array formula with VBA
 

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?
--
RMC,CPA

How to enter array formula with VBA
 

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)


"R. Choate" wrote in message
...
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?
--
RMC,CPA

How to enter array formula with VBA
 

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


"Bob Phillips" wrote in message ...
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)


"R. Choate" wrote in message
...
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?
--
RMC,CPA

How to enter array formula with VBA
 

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

In article ,
says...
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


"Bob Phillips" wrote in message ...
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))"

How to enter array formula with VBA
 

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


"Tushar Mehta" wrote in message
...
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

In article ,
says...
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


"Bob Phillips" wrote in message ...
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))"