I need a sum function that takes data from a matrix and sums certain row tother
Example)
Data is in cells A1,B1,C1,D1,E1,F1
and A2,B2,C2,D2,E2,F2
I need the vba to in cell A3 sum A1 to C1
then in cell B3 sum B1 to D1
then in cell C3 sum C1 to E1
then do the same thing for the next row beginning in Cell A4
Please help!
Do you mean VBA? Here's the simple formula which can be dragged across:
Please Login or Register to view this content.
If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)
You can't do one thing. XLAdept
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin
Yes the amount of data I need to do this for is extrmely large (over 1 million cells) and causes excel to crash with the number of calculations and file size the formulas create. When I transition this to a vba formula the file size become much smaller.
So far i have something like
Range("B3").offset(i,k) = worksheetfunction.sum(Range("A1"), Range("A2") etc. but this only is resulting in the my matrix populating the same value for everything.
If this makes sense.
I am perplexed by the idea that 1,000,000 simple SUM formulas would crash Excel? SUM is not a volatile function so each SUM would calc and settle, not causing further overhead. 1,000,000 one time calcs is not a problem for Excel.
As for file size, maybe, that's a pretty small formula:
=SUM(A1:C1)
...only nine characters, not that many more characters than if you just had the answers in those cells.
But, you can still put the formulas in, then remove them, leaving the values behind:
Please Login or Register to view this content.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Ended up solving it usinging some defined variables and nested offset functions
note I predefined i, k, j
Please Login or Register to view this content.
Last edited by JBeaucaire; 07-25-2012 at 07:24 PM. Reason: Added code tags, as per forum rules. Don't forget!
Glad you figured it out.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks