SUMIFS in Programming
 

Below is waht ideally I would like to do with a procedure and I know
this will not work with n in a formula, it was just to show the concept
of what I was after.
I would appreciate any guidance.

Graham


For n = 37 To 44

Cells(3, n).Select

ActiveCell.FormulaR1C1 = _

"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2Cn,R15C4: R600C4,R3C35)"

Next n

SUMIFS in Programming
 

Hi Graham

One way

Cells(3, 37).Formula = _
"=SUMIFS($N$15:$N$600,$M$15:$M$600,$AK$2,D$15:$D$6 00,AI$3)"

Cells(3, 37).Copy Range(Cells(3, 38), Cells(3, 44))


--
Regards
Roger Govier

Graham wrote:
Below is waht ideally I would like to do with a procedure and I know
this will not work with n in a formula, it was just to show the concept
of what I was after.
I would appreciate any guidance.

Graham


For n = 37 To 44

Cells(3, n).Select

ActiveCell.FormulaR1C1 = _

"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2Cn,R15C4: R600C4,R3C35)"

Next n

SUMIFS in Programming
 

Maybe simply this -

dim sf as string

sf = "=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2C,R15C4:R 600C4,R3C35)"
Range("AK3:AR3").FormulaR1C1 = sf

but note a very subtle difference with the relative addressing, which if you
manually want to extend I suspect will work better for you. Ie R2C vs R2Cn

Otherwise -

s1 = "=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2C"
s2 = ",R15C4:R600C4,R3C35)"

For n = 37 To 44
sf = s1 & n & s2
Cells(3, n).FormulaR1C1 = sf
Next

Regards,
Peter T


"Graham" wrote in message
...
Below is waht ideally I would like to do with a procedure and I know this
will not work with n in a formula, it was just to show the concept of what
I was after.
I would appreciate any guidance.

Graham


For n = 37 To 44

Cells(3, n).Select

ActiveCell.FormulaR1C1 = _

"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2Cn,R15C4: R600C4,R3C35)"

Next n

SUMIFS in Programming
 

Try

Cells(3, 37).Resize(, 8).FormulaR1C1 = _
"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2C[],R15C4:R600C4,R3C35)"


--

HTH

Bob

"Graham" wrote in message
...
Below is waht ideally I would like to do with a procedure and I know this
will not work with n in a formula, it was just to show the concept of what
I was after.
I would appreciate any guidance.

Graham


For n = 37 To 44

Cells(3, n).Select

ActiveCell.FormulaR1C1 = _

"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2Cn,R15C4: R600C4,R3C35)"

Next n

SUMIFS in Programming
 

Many thanks to all for the responses which I will get time to try out
tonight. I much appreciate your help and the exremely fast replies.
I am very grateful

Graham



On 07/04/2010 10:43, Graham wrote:
Below is waht ideally I would like to do with a procedure and I know
this will not work with n in a formula, it was just to show the concept
of what I was after.
I would appreciate any guidance.

Graham


For n = 37 To 44

Cells(3, n).Select

ActiveCell.FormulaR1C1 = _

"=SUMIFS(R15C14:R600C14,R15C13:R600C13,R2Cn,R15C4: R600C4,R3C35)"

Next n