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)

"dorre" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
>

[Tom Ogilvy]

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

[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" <[email protected]> wrote in message
news:[email protected]...
> With Sheet1
> .Range("A10:C70").FormulaArray = "=SUM(LARGE((" .... etc...
> "
> End With
>
>
> --
> HTH
>
> Bob Phillips
>
> (remove nothere from email address if mailing direct)
>
> "dorre" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>>
>
>

[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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> message news:[email protected]...
>> With Sheet1
>> .Range("A10:C70").FormulaArray = "=SUM(LARGE((" ....
>> etc...
>> "
>> End With
>>
>>
>> --
>> HTH
>>
>> Bob Phillips
>>
>> (remove nothere from email address if mailing direct)
>>
>> "dorre" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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
>>>
>>>
>>>
>>
>>
>
>

[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:D$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


"dorre" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>