R1CI Sum

[billandrew]

Good evening

A bit stumped on how to sum a range utilizing R1C1. My code is:

Thanks for any help.

Sub sumeverycolum()




Dim lastcolumn As Long
Dim lcol As Long
Dim lastrow As Long


lastcolumn = Cells.Find(what:="*", after:=Range("A1"), _
searchorder:=xlByColumns, searchdirection:=xlPrevious).Column


For lcol = 1 To lastcolumn

lastrow = Cells(Rows.Count, lcol).End(xlUp).Row

With Cells(lastrow + 1, lcol)

.FormulaR1C1 = "=Sum(R1C:R" & lastrow & "D)"



End With
Next

End Sub

 

[MARK858]

Stumped in what way?

 

[billandrew]

The code is breaking at the FormulaR1C1

 

[MARK858]

Then you are missing a ".

 

[billandrew]

Where?

 

[MARK858]

Edit: actually the D isn't making much sense. What is the D supposed to do?

 

[billandrew]

The "D" is representing Column D. Should this be lcol?

 

[shg]

The "D" is representing Column D.

.FormulaR1C1 = "=Sum(R1C:R" & lastrow & "C4)"

 

[MARK858]

The "D" is representing Column D. Should this be lcol?

Trying to work out what you are exactly trying to do if you are fixing it as column D when you are looping through all the columns?

Maybe you are just trying to sum the current column? if so it is just

.FormulaR1C1 = "=Sum(R1C:R" & lastrow & "C)"

or maybe multiple columns say 4 columns at a time then it would be

.FormulaR1C1 = "=Sum(R1C:R" & lastrow & "C[3])"

Obviously what shg has posted will give you the fixed column D if that is actually what you need.

 

[billandrew]

That does not sum the columns correctly.

Below is a sample of the data. The data starts in cells A1

Thank You


[TABLE="width: 288"]
<colgroup><col width="72" span="4" style="width:54pt"> </colgroup><tbody>[TR]
[TD="width: 72, align: right"]79.2[/TD]
[TD="width: 72, align: right"]46.2[/TD]
[TD="width: 72, align: right"]62.7[/TD]
[TD="width: 72, align: right"]13.4[/TD]
[/TR]
[TR]
[TD="align: right"]81.3[/TD]
[TD="align: right"]55.5[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD="align: right"]103.2[/TD]
[TD="align: right"]69.7[/TD]
[TD="align: right"]79.7[/TD]
[TD="align: right"]3.8[/TD]
[/TR]
[TR]
[TD="align: right"]93.1[/TD]
[TD="align: right"]50.8[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]3.1[/TD]
[/TR]
[TR]
[TD="align: right"]96.3[/TD]
[TD="align: right"]69.3[/TD]
[TD="align: right"]85.8[/TD]
[TD="align: right"]10.7[/TD]
[/TR]
[TR]
[TD="align: right"]59.4[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]39.6[/TD]
[TD="class: xl63, align: right"]17.8[/TD]
[/TR]
[TR]
[TD="align: right"]78.6[/TD]
[TD="align: right"]40.1[/TD]
[TD="align: right"]70.4[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="align: right"]183.5[/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]164.5[/TD]
[TD="class: xl63"][/TD]
[/TR]
[TR]
[TD="align: right"]130.8[/TD]
[TD="align: right"]74.8[/TD]
[TD="align: right"]107.6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]132[/TD]
[TD="align: right"]83.4[/TD]
[TD="class: xl63, align: right"]757.3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]99.3[/TD]
[TD="align: right"]57.7[/TD]
[TD="class: xl64"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]82.9[/TD]
[TD="align: right"]46.1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]275.6[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]285.4[/TD]
[TD="class: xl63"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]325.4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]