 Search:

# count the sum of a column

Asked By: Lorenzo    Date: Nov 26    Category: MS Office    Views: 1888

here i have a extremely simple question: how to write codes that can
count the sum of a column?
i've tried a circular counter but the program alwayse reported a
mismatch error.
My original code is:
Dim i as long
k = 0
For i = 2 to 100
k = k + thisworkbook.worksheets("sheet1").cells(1, i)
next i

Share:

I think you can just do something like

x = Sum(A1:A100)

not sure on the exact syntax to replace the 100 with an 'i' though.

In the past, I have typically had a problem with using the For i = 2 to 100
method, so instead, I use the following syntax.

Do While ActiveCell.Value <> ""
Perform calculation steps
Loop

is it a type mismatch between the data and the long data type?

Try declaring i as a double instead maybe?

I"ve tried the methods suggested by all of you
but it didn't help.

first of all, I declared i and k as double but still got mismatch
error.

secondly, I used the function sum(A2:A100), but it was reported that
there was a syntax error.

lastly, I wanna calculate the sums of the columns respectively, I'm
afraid using Do while activecell.value <>"" would result in a sum  of
all of the cells in the worksheet.

So, I haven't had my problem resolved. But I appreciate all the

sorry I couldn;t help, still a bit new at this myself.

If you're still getting a mismatch, then mayb see if something like
this works:

Dim i as Double
dim k as Double
k = 0
For i = 2 to 100
if isNumeric(k + thisworkbook.worksheets("sheet1").cells(1, i)) then
k = k + thisworkbook.worksheets("sheet1").cells(1, i)
end if
next i

If IsNumeric(thisworkbook.worksheets("sheet1").cells(1,i)) Then
...

not as written below.

Dim dblSum As Double
' Sum column  A of Sheet1 in thisworkbook
dblSum =
Application.WorksheetFunction.Sum(ThisWorkbook.Worksheets("Sheet1").Columns(1))

I think the Mismatch error  is caused by a cell containing text instead
of a number.

Also your original  code loops across the columns in row 1
.cells(1, i)
whereas the question  suggests you want to move down the rows in a column.

dim sum  as integer
dim i as integer
dim objSheet as worksheet
set objSheet=sheets("Sheet1") 'Sheet1 is worksheet Name
sum=0
for i=1 to 100
sum=objsheet.cells(i,2).value+sum
next
'Sum contains the final result

Let me know whether it worked fine or not

Didn't find what you were looking for? Find more on count the sum of a column Or get search suggestion and latest updates.