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

79.2	46.2	62.7	13.4
81.3	55.5	60	3.8
103.2	69.7	79.7	3.8
93.1	50.8	87	3.1
96.3	69.3	85.8	10.7
59.4	34.4	39.6	17.8
78.6	40.1	70.4
183.5	103	164.5
130.8	74.8	107.6
132	83.4	757.3
99.3	57.7
82.9	46.1
275.6
285.4
325.4

<colgroup><col width="72" span="4" style="width:54pt"> </colgroup><tbody>
</tbody>