Argh! Varied SUM ranges! Please help!

Posted by Dave on January 07, 2002 8:08 AM

Hi.
what i want is a way to say (in VBA Excel 97):

Put this formula in the cell i'm in "=SUM('one up from the cell i'm in':'the top of the column except the title row')"

in other words a way to sum all the data above the current point (which can vary in row number but, not column).

i know it should be easy but, i'm tearing my hair out!! Please help.

Thanks,

Dave

Posted by Dan on January 07, 2002 12:15 PM

Try this

Assuming that your title row is always in Row 1. Select the cell that you want the total in and run the following macro:

Sub SumUp()

Dim Total As Long
For i = 2 To ActiveCell.Row - 1
Total = Cells(i, ActiveCell.Column).Value + Total
Next i
ActiveCell.Value = Total

End Sub

HTH. - Dan

Posted by Dave on January 08, 2002 4:43 AM

Re: Try this

Dan,

Tried to add the code you suggested but, i get a "type mismatch error" in the line:

Total = Cells(i, ActiveCell.Column).Value + Total

Any ideas?

If you like, i'll post the rest of the macro as it stands (its not vey long at the moment...), if you think that might be it!

Thanks,
Dave

:assuming that your title row is always in Row 1. Select the cell that you want the total in and run the following macro:

Posted by Dan on January 08, 2002 6:47 AM

Re: Try this

The reason is that there is text in one or more of the cells in that column, so it can't add it. Replace that line with:

If IsNumeric(Cells(i, ActiveCell.Column).Value) Then Total = Cells(i, ActiveCell.Column).Value + Total

That should take care of the problem.

Posted by Dave on January 08, 2002 8:04 AM

Cheers, worked a treat.

Thanks a lot, that worked a treat! :)
Dave The reason is that there is text in one or more of the cells in that column, so it can't add it. Replace that line with: If IsNumeric(Cells(i, ActiveCell.Column).Value) Then Total = Cells(i, ActiveCell.Column).Value + Total That should take care of the problem.