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

 

[billandrew]

I am trying to sum each column individually.

 

[MARK858]

Then see the first code line I posted.

 

[MARK858]

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>

The first code I posted gets the same result as doing it manually for me (H1:H4 in the below are manual formula, the yellow highlighted cells were with the code).

Excel 2010
	A	B	C	D	E	F	G	H
1	79.2	46.2	62.7	13.4				2106
2	81.3	55.5	60	3.8	0	0	0	731
3	103.2	69.7	79.7	3.8				1514.6
4	93.1	50.8	87	3.1				52.6
5	96.3	69.3	85.8	10.7
6	59.4	34.4	39.6	17.8
7	78.6	40.1	70.4	52.6
8	183.5	103	164.5
9	130.8	74.8	107.6
10	132	83.4	757.3
11	99.3	57.7	1514.6
12	82.9	46.1
13	275.6	731
14	285.4
15	325.4
16	2106
Sheet2

Cell Formulas
Range	Formula
E2	=SUM(E$1:E$1)
F2	=SUM(F$1:F$1)
G2	=SUM(G$1:G$1)
H1	=SUM(A1:A15)
H2	=SUM(B1:B12)
H3	=SUM(C1:C10)
H4	=SUM(D1:D6)
D7	=SUM(D$1:D$6)
C11	=SUM(C$1:C$10)
B13	=SUM(B$1:B$12)
A16	=SUM(A$1:A$15)

If you are getting different results I would expand your number of decimal places as I would suspect that your values aren't exact as displayed.

 

[billandrew]

Mark858

You are 100% correct the formula you sent works perfect. Thank You for you patience, not sure how I missed it.

One question - Does the "C refer to the column, any column with data?

 

[MARK858]

You are using R1C1 notation.

The R stands for Row, the C for Column.
C without a number means the same column as the cell the formula is in, if you had C[1] it would refer to the next column to the right and C[3] the column 3 to the right.

 

[billandrew]

Thank you again for all your time.