VBA code simpler?

[mohavv]

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

 

[carlo]

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

 

[JMB]

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

 

[mohavv]

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








- Show quoted text -

Thanks works fine.

But I don't get it.

Range("D1:I1").Offset(lastrow + 1, 0).Formula = "=sum(D13" & 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

 

[Dana DeLouis]

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.