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

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


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"dorre" wrote in message
...
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 & 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


"Bob Phillips" wrote in message
...
With Sheet1
.Range("A10:C70").FormulaArray = "=SUM(LARGE((" .... etc...
"
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

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


"Bob Phillips" wrote in
message ...
With Sheet1
.Range("A10:C70").FormulaArray = "=SUM(LARGE((" ....
etc...
"
End With


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

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

works like a charm!
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:D$70=D10)*...etc ..." ), then G$10:G$70...
etc.

Thanks, Dorre

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


"dorre" wrote in message
...
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:D$70=D10)*...etc ..." ), then
G$10:G$70... etc.

Thanks, Dorre

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

--
Regards,
Tom Ogilvy


"dorre" wrote:

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