programatically entering a CSE formula

[dorre]

hi folks

What is the correct way to enter a CSE formula into a range of cells?

With Sheet1
.Range("A10:C70").Formula = "=SUM(lARGE((" .... etc... "
End With

This works for non-CSE formulas, but how do I enter CSE ones?

much thanks
Dorre

 

[Bob Phillips]

With Sheet1
.Range("A10:C70").FormulaArray = "=SUM(LARGE((" .... etc...
"
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

the better name is Array Formula. Many people will have no idea what you
mean when you say CSE formula

 

[dorre]

Bob & Tom

Thanks for your quick responses. My original question didn't make one
thing very clear. The Array Formula needs to be dragged down because the
formula changes in each cell. When I use the .FormulaArray property, I get
the same formula in each cell.

With Sheet1
.Range("C10:C70").FormulaArray = "=SUM(LARGE((A$10:A$70=A10)*...etc ..."
End With

can this be done?
TQ, Dorre

 

[Chip Pearson]

Try something like the following:

With Sheet1
.Range("C10").FormulaArray = "your formula here"
.Range("C10:C70").FillDown
End With


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[dorre]

works like a charm!
Dorre

 

[dorre]

Chip - if I might be allowed a followup question. The code you suggested
works wonderfully.

With Sheet1

.Range("C10").FormulaArray = "=SUM(LARGE((A$10:A$70=A10)*...etc ..."

.Range("C10:C70").FillDown
End With

But... I need to use it in columns C, F, I... . I can use .Offset(0,3)
before the .FormulaArray and the .FillDown, but is it easy to also offset
the formula in quotes?

(to "=SUM(LARGE((D$10$70=D10)*...etc ..." ), then G$10:G$70...
etc.

Thanks, Dorre

 

[Chip Pearson]

Try

With Sheet1
.Range("C10").FormulaArray = "your formula"
.Range("C10:C70").FillDown
.Range("C10:G70").FillRight
End With



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com