VBA code simpler?
 

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

VBA code simpler?
 

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

VBA code simpler?
 

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

VBA code simpler?
 

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

VBA code simpler?
 

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