how to sum a varying range

[kiransalu]

I have an excel sheet which fetches data from Oracle DB using OO4O macros. There are 3 columns from the DB.

A B C
1) 1 3 4
2) 3 5 6
3) 7 4 7
Total 11 12 17

Using VBA i have to sum column A (1+3+7=11) and compare with the total.(last row). Rows are dynamic based on the data from oracle. How do i calculate from A1 to the end of row(one row above the total row)

 

[duncdude]

i'm no Excelpert but wouldn't this kind of thing do?

=SUM(A:A)


Kind Regards
Duncan

 

[kiransalu]

Sum(A:A) counts all the rows. Out of 10 rows i should count only the first 9 rows and then match it with the 10th row.

 

[duncdude]

Ah! i apologise


Kind Regards
Duncan

 

[SkipVought]

Hi,

Think of this as an OPPORTUNITY to have a thinking-out-of-the-box moment.

Why do TOTALS get postioned at the BOTTOM of a table?

Could TOTALS be better positioned elsewhere to the benefit of both the spreadsheet designer AND the spreadsheet user?

What are the capabilities of aggregate functions like SUM?

Inquiring mindes NEED to know!

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[UniqueFD]

Will an Excel macro suffice? The following will place a check total two rows below your totals in each column (if you run it from somewhere in the first column, of course!)

Sub Macro1()
Do While (ActiveCell <> 0):
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Select
    ActiveCell.FormulaR1C1 = "=SUM(R1C:R[-3]C)-R[-2]C"
    ActiveCell.Offset(-2, 0).Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, 1).Select
    Loop
End Sub

(Forgive me if this isn't the greatest solution in the world - this is my first macro and I have VERY limited knowledge.)

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[SkipVought]

Totals at the BOTTOM of a table is a vestage of paper, pencil, adding machine and aggregation verification. Think about it!

Why would you FORCE your user to SEARCH FOR THE TOTALS, when with electronic spreadsheets, one can place aggregate information, ANYWHERE, even at the TOP -- right out front -- NO SEARCHING for the "bottom line".

IMHO, all aggregates should be AT THE TOP, with the exception of subtotals, which by their very nature, must be embedded in the list to be meaningful, or some compelling user requirement that mitigates against such.

I usually do this even for Pivotables, which place totals at the right/bottom. I put totals at the left/top.

Make more useable for the user!

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[UniqueFD]

Hi Skip,

I agree with your comments and follow this convention when I can (sometimes creating a separate summary/check totals sheet altogether). With what was given, however, this seemed the simplest solution.

As I confessed earlier, I am a novice in the VBA arena. I would value your thoughts on an alternative, just to broaden my thinking/experience.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)

 

[I2007]

Can't you do this befoure you paste the information to excel. I mean in the macro collecting data from oracle and use SUM AS command there. Just a thought, I'm a novice too.

i2007

 

[xlbo]

1stly - I agree with Skip wholeheartedly but if the program cannot be changed, it is that which is outputting the data so.....

To enter a formula via VBA:

Range("Cell_ref").formula = "=Formula Goes Here"

There are 2 FAQs in this forum for finding the last row in a column of data. So, you would need to find the last row and subtract 1 to get the last row to sum.

Range("A" & lastRow + 1).formula = "=SUM(A1:A" & lastRow - 1 & ")"

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[duncdude]

GOT IT!!!

Took me flipping ages to suss it...

A1:A? full of values

and this formula in B1:-

=SUM(A1:INDIRECT("A"&COUNT(A:A)-1))


Kind Regards
Duncan

 

[kiransalu]

Excellent.

Thank you very much for all of your answers.

 

[SkipVought]

Ahhhhhhh....

"Eureka!" -- Far better than following the dotted line. ;-)

Skip,
[sub]
[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH! [/sub]

 

[duncdude]

Cheers Skip!


Kind Regards
Duncan