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


[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]

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.