• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Sum procedure

I

irisqueiroz

Member
Hi,


I have a data table that is in monthly frequency and i'd like to transform it in a quarter frequency (these data are in sheet1).


The data starts in B11 cell ... So i'd like to plot in an other sheet (suppose sheet2):

cell A1 = sum(sheet1!b11:d11)

cell A2 = sum(sheet1!e11:g11)

cell A3 = sum(sheet1!h11:j11)

.........

till the end of line 11 of sheet 1.


Thank you!
 
Irisqueiroz


Firstly, Welcome to the Chandoo.org forums.


Can you post a sample file showing us your data layout and what you require?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook


There are several posts on Quarterly reporting options here at Chandoo.org

Use the Google Search box here: Top Right corner of this screen and type in Quarterly
 
Hi, irisqueiroz!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Type this in A1 of sheet Hoja3 assuming your data is in sheet Hoja2, and copy down as needed:


=SUMA(INDIRECTO(DIRECCION(11;FILA()*3-1;4;1;"Hoja2")&":"&DIRECCION(11;FILA()*3+1;4;1))) -----> in english: =SUM(INDIRECT(ADDRESS(11,ROW()*3-1,4,1,"Hoja2")&":"&ADDRESS(11,ROW()*3+1,4,1))) -----> in portuguese: =SOMA(INDIRETO(ENDEREÇO(11;LIN()*3-1;4;1;"Hoja2")&":"&ENDEREÇO(11;LIN()*3+1;4;1)))


Regards!
 
Hui,


the link is here: https://www.dropbox.com/s/zwkrqj1q1x44wsa/STN%20-%20Resultado%20do%20governo%20central%20%28mensal%29.xls


The sheet that i was talking about is called "mensal"...

So, i'd like to make a vba code that creates another sheet that in column A plots:


cell A1 = sum(sheet("mensal")!b12:d12)

cell A2 = sum(sheet("mensal")!e12:g12)

cell A3 = sum(sheet("mensal")!h12:j12)

.........

till the end of line 11 of sheet "mensal".


Being more complete, in column B (of the new sheet):


.........

till the end of line 12 of sheet "mensal".


and so on....
 
Hi, irisqueiroz!


Give a look at this file:

https://dl.dropbox.com/u/60558749/VBA%20Sum%20procedure%20-%20STN%20-%20Resultado%20do%20governo%20central%20%28mensal%29%20%28for%20irisqueiroz%20at%20chandoo.org%29.xlsm


I added a new sheet "Periódico" where you'll find a table of periodicity and ranges (rows & columns) for source and target cells. Changing B1 cell value, changes the grouping criteria in column A.


I see you titled this topic as VBA Sum procedure but unless required specifically there's no need of any code. Just advise if any issue.


Regards!
 
Thank you, SirJB7!


I really worked fine...

But in fact, i spent hours trying to make a VBA code for this procedure, and I'd like to know if it's possible to make it. Just in terms of learning!


Thanks again!
 
Hi, irisqueiroz!


Please download again the file from same previous link, go to sheet "PeriódicoMacro", click on cyan button... et voilá!


The code might appear complex, but the main part you would have been looking for might be this (the For K loop):

-----

[pre]
Code: 
With rngS
' from / to
I = .Cells(1, 1).Column
J = .Cells(1, 1).End(xlToRight).Column
' number
iRows = Int((J - I + 1) / iMonths) + Sgn((J - I + 1) Mod iMonths)
' clear
rngT.Columns(1).EntireColumn.ClearContents
' fill
For K = 1 To iRows
N = 0
For L = 1 To iMonths
N = N + .Cells(1, (K - 1) * iMonths + L).Value
Next L
rngT.Cells(K, 1).Value = N
Next K
End With
[/pre]
-----


Just advise if any issue.


Com os meus melhores cumprimentos!


PS: community clarification... aka Best regards!
 
Hi, irisqueiroz!


Let me try to dissect the procedure:


a) I integer variable holds the first column of source range (2, B)

b) J integer variable holds the last column of source range (187, GE)

c) iRows integer variable is the number of rows to be created (187 - 2 + 1)/3 + <1 more for the remaining, if not zero> (3, Trimestral)

d) we clear the output column, just in case

e) For K = 1 to iRows: generate the index for each row

f) N for accumulating the monthly values

g) For L = 1 to iMonths (3 in this case): generate the index for each period

h) N gets added by the column number (K-1)*3+L

i) L loop ends

j) target range at row K column 1 holds the new calculated value

k) K loop ends


Hope it helps.


Regards!
 
Hi, irisqueiroz!

Glad if it helped you. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted. Or just drop me an email.

Regards!
 
You must log in or register to reply here.
Back
Top