Sum a column

  • Thread starter Thread starter newbie04
  • Start date Start date
N

newbie04

i have a series of data :

2 2
5 5
7
1 1
0 0
3 3
6 6
10
2 2
9 9
11

The LHS is the original data while the RHS is the data i wanna obtain
My doubts is that what is the code for summing?

Thank you
 
I know the logic to do this scenerio, except i dunno how the summin
part works. My code is shown below

Worksheets(a).cells(i,j)Select
ActiveCell.formula = "=SUM(R[-z]C:R[-1]C)"


It seems that the second line is wrong. Do someone know the reason?

Thk u
 
newbie04,
How do you know which values in LHS are true values and which are sums of
the numbers above ?

NickHK
 
Hi nick,

okie, my original data is shown
2
5

1
0
3
6

2
9

And i wanna get the result:
2
5
7
1
0
3
6
10
2
9
11

I know the logic to scan the empty column and do the sum, but it seem
that z is not valid here. May i know why?

Worksheets(a).cells(i,j)Select
ActiveCell.formula = "=SUM(R[-z]C:R[-1]C)
 
To sum all the numbers in column A...
=SUM(a1:a11)
To sum all the numbers in column B...
=SUM(b1:b11)

----- newbie04 > wrote: -----

i have a series of data :

2 2
5 5
7
1 1
0 0
3 3
6 6
10
2 2
9 9
11

The LHS is the original data while the RHS is the data i wanna obtain.
My doubts is that what is the code for summing?

Thank you.
 
but how abt if my number is represent by an alphabet?

To sum all the numbers in column A...
=SUM(a1:a11)

To sum all the numbers in column B...
=SUM(b1:b11)

If my 1=i, 11=j,
then how do i do this?
 
newbie04,
If your data is split like that, with an empty line between, one way is to
use Data --> Subtotal menu to achieve what you want. That way Excel takes
care of which are real values and which are (sub) totals.

NickHK
 
newbie04,
In code you need something like:
activecell.Formaula="=SUM(B" & i & ":B" & j & ")"

NickHK
 
Sub testing()


z = 0
For m = 3 To Sheets.Count
For i = 6 To 200
If Worksheets(m).Cells(i, 15) = Worksheets(m).Cells(i + 1, 15) Then
z = z + 1
Else
If Worksheets(m).Cells(i, 15) <> Worksheets(m).Cells(i + 1, 15) An
Worksheets(m).Cells(i, 1) <> "" Then
Worksheets(m).Rows(i + 1).Font.Bold = True
Worksheets(m).Cells(i + 1, 9) = "=SUM(I" & (i - z) & ":I" & i
")"
Worksheets(m).Cells(i + 1, 10) = "=SUM(J" & (i - z) & ":J" & i
")"
Worksheets(m).Cells(i + 1, 11) = "=SUM(K" & (i - z) & ":K" & i
")"
Worksheets(m).Cells(i + 1, 13) = "=SUM(M" & (i - z) & ":M" & i
")"
z = 0
End If
End If
Next i
Next m

End Sub


Here's the code. i seem alrite to run in single sheet, but error
happen when it has to do the task over several sheets.
I guess the error are the SUM.... as it is not defined which column t
be taken from the sheets.

So how i can solve this?

Thks
 
newbie04,
Comments in line.

Also, what errors are you getting ?

NickHK


newbie04 > said:
Sub testing()


z = 0
For m = 3 To Sheets.Count
For i = 6 To 200
Click to expand...

'---- Use With Worksheets(m) to tidy the code --------
If Worksheets(m).Cells(i, 15) = Worksheets(m).Cells(i + 1, 15) Then
z = z + 1
Else
Click to expand...

'------ Not needed as you tested this above and must be true to reach
re --
 
Back
Top