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))"