Hi,

I have a feeling that this part of my macro can be done simpler.
But I don't know how.

Range("D" & LastRow + 2).Formula = "=sum(D13:D" & LastRow & ")"
Range("E" & LastRow + 2).Formula = "=sum(E13:E" & LastRow & ")"
Range("F" & LastRow + 2).Formula = "=sum(F13:F" & LastRow & ")"
Range("G" & LastRow + 2).Formula = "=sum(G13:G" & LastRow & ")"
Range("H" & LastRow + 2).Formula = "=sum(H13:H" & LastRow & ")"
Range("I" & LastRow + 2).Formula = "=sum(I13:I" & LastRow & ")"


Thanks in advance for your help.

Cheers,

Harold

Dim chr_ as string

For i = 4 to 9
chr_ = chr(64 + i)
Range(chr_ & LastRow + 2).Formula = "=sum(" & chr_ & "13:" & chr_
& " & LastRow & ")"
next i

is that what you're looking for?

hth

Carlo

On Nov 28, 11:41 am, mohavv wrote:
Hi,

I have a feeling that this part of my macro can be done simpler.
But I don't know how.

Range("D" & LastRow + 2).Formula = "=sum(D13:D" & LastRow & ")"
Range("E" & LastRow + 2).Formula = "=sum(E13:E" & LastRow & ")"
Range("F" & LastRow + 2).Formula = "=sum(F13:F" & LastRow & ")"
Range("G" & LastRow + 2).Formula = "=sum(G13:G" & LastRow & ")"
Range("H" & LastRow + 2).Formula = "=sum(H13:H" & LastRow & ")"
Range("I" & LastRow + 2).Formula = "=sum(I13:I" & LastRow & ")"

Thanks in advance for your help.

Cheers,

Harold

try:
Range("D1:I1").Offset(lastrow + 1, 0).Formula = "=sum(D13:D" & lastrow & ")"


"mohavv" wrote:


Hi,

I have a feeling that this part of my macro can be done simpler.
But I don't know how.

Range("D" & LastRow + 2).Formula = "=sum(D13:D" & LastRow & ")"
Range("E" & LastRow + 2).Formula = "=sum(E13:E" & LastRow & ")"
Range("F" & LastRow + 2).Formula = "=sum(F13:F" & LastRow & ")"
Range("G" & LastRow + 2).Formula = "=sum(G13:G" & LastRow & ")"
Range("H" & LastRow + 2).Formula = "=sum(H13:H" & LastRow & ")"
Range("I" & LastRow + 2).Formula = "=sum(I13:I" & LastRow & ")"


Thanks in advance for your help.

Cheers,

Harold

On Nov 28, 4:00 pm, JMB wrote:
try:
Range("D1:I1").Offset(lastrow + 1, 0).Formula = "=sum(D13:D" & lastrow & ")"



"mohavv" wrote:

Hi,

I have a feeling that this part of my macro can be done simpler.
But I don't know how.

Range("D" & LastRow + 2).Formula = "=sum(D13:D" & LastRow & ")"
Range("E" & LastRow + 2).Formula = "=sum(E13:E" & LastRow & ")"
Range("F" & LastRow + 2).Formula = "=sum(F13:F" & LastRow & ")"
Range("G" & LastRow + 2).Formula = "=sum(G13:G" & LastRow & ")"
Range("H" & LastRow + 2).Formula = "=sum(H13:H" & LastRow & ")"
Range("I" & LastRow + 2).Formula = "=sum(I13:I" & LastRow & ")"

Thanks in advance for your help.

Cheers,

Harold- Hide quoted text -

- Show quoted text -

Thanks works fine.

But I don't get it.

Range("D1:I1").Offset(lastrow + 1, 0).Formula = "=sum(D13:D" & lastrow
& ")"

After reading it I thought it would give met "=sum(D13:"to lastrow")
in the 6 cells. So 6 times the sum of column D.
What does make it change its range to the right column?

Cheers,

Harold

Would this work?

Sub Demo()
Dim LastRow As Long
LastRow = 20

Cells(LastRow + 2, 4).Resize(1, 6).FormulaR1C1 = "=SUM(R13C:R[-2]C)"
End Sub

It reads like this:
Sum from Row 13, same column, to 2 rows above reference, same column.

--
HTH :)
Dana DeLouis
Windows XP & Excel 2007


"mohavv" wrote in message
...

Hi,

I have a feeling that this part of my macro can be done simpler.
But I don't know how.

Range("D" & LastRow + 2).Formula = "=sum(D13:D" & LastRow & ")"
Range("E" & LastRow + 2).Formula = "=sum(E13:E" & LastRow & ")"
Range("F" & LastRow + 2).Formula = "=sum(F13:F" & LastRow & ")"
Range("G" & LastRow + 2).Formula = "=sum(G13:G" & LastRow & ")"
Range("H" & LastRow + 2).Formula = "=sum(H13:H" & LastRow & ")"
Range("I" & LastRow + 2).Formula = "=sum(I13:I" & LastRow & ")"


Thanks in advance for your help.

Cheers,

Harold