MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

count the sum of a column

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

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



8 Answers Found

Answer #1    Answered By: Keith Marshall     Answered On: Nov 26

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.

Answer #2    Answered By: Timothy Patterson     Answered On: Nov 26

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

Answer #3    Answered By: Jezza Brown     Answered On: Nov 26

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

Try declaring i as a double instead maybe?

Answer #4    Answered By: Norman Ray     Answered On: Nov 26

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

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
suggestions. Any more leads?

Answer #5    Answered By: Leon Evans     Answered On: Nov 26

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

Answer #6    Answered By: Garai Chalthoum     Answered On: Nov 26

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

not as written below.

Answer #7    Answered By: Caitlin Brown     Answered On: Nov 26

How about this,

Dim dblSum As Double
' Sum column  A of Sheet1 in thisworkbook
dblSum =

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.

Answer #8    Answered By: Mamie Wallace     Answered On: Nov 26

I think this code  could help you

dim sum  as integer
dim i as integer
dim objSheet as worksheet
set objSheet=sheets("Sheet1") 'Sheet1 is worksheet Name
for i=1 to 100
'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.