programatically entering a CSE formula

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" <dorre@idirect.com> wrote in message
news:%23IM3DR1UGHA.4660@tk2msftngp13.phx.gbl...
> 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
>
>
>

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

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" <bob.phillips@notheretiscali.co.uk> wrote in message
news:OkVWOY1UGHA.4452@TK2MSFTNGP12.phx.gbl...
> With Sheet1
> .Range("A10:C70").FormulaArray = "=SUM(LARGE((" .... etc...
> "
> End With
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "dorre" <dorre@idirect.com> wrote in message
> news:%23IM3DR1UGHA.4660@tk2msftngp13.phx.gbl...
>> 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" <dorre@idirect.com> wrote in message
news:uFlMWu1UGHA.6112@TK2MSFTNGP10.phx.gbl...
> 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" <bob.phillips@notheretiscali.co.uk> wrote in
> message news:OkVWOY1UGHA.4452@TK2MSFTNGP12.phx.gbl...
>> With Sheet1
>> .Range("A10:C70").FormulaArray = "=SUM(LARGE((" ....
>> etc...
>> "
>> End With
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "dorre" <dorre@idirect.com> wrote in message
>> news:%23IM3DR1UGHA.4660@tk2msftngp13.phx.gbl...
>>> 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" <dorre@idirect.com> wrote in message
news:u28us$1UGHA.2276@tk2msftngp13.phx.gbl...
> 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
>
>
>