+ Reply to Thread
Results 1 to 4 of 4

Columbia student exercise, Monthly > Quarterly data by Macro exercise

  1. #1
    Registered User
    Join Date
    04-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    Columbia student exercise, Monthly > Quarterly data by Macro exercise

    I'm trying to learn VBA and I know it would probably be easier to do manually but I would like to learn how to program this as a macro for the sake of the exercise and so that I will better understand how VBA works. Thank you for your time in reading the following:

    The attached excel file has monthly data I would like to be able to press a button that will basically start with the data on worksheet 'INF (start)' and after selecting the text from B2:F25 and running the macro you will end up with the data shown on 'INF (desired end)'

    Please critique my thought process thus far and then give whatever advice you may have on how to continue.
    I figured it would be easiest to start by converting column A to YYYYMMDD format and then 1) generating the figures in 'INF (desired end)' I2:I9 by using the MID() function to grab the 08... and also using that to pull the months and using a ROUNDUP() to get which quarter is relevant but I have no idea how to sum the cells in columns B through F based on criteria defined by the first cell in the row (which obviously becomes one column further each column)

    Thank you for your help. This is an exercise I am doing as I am writing an economics thesis that will involve very significant volumes of data and I need to learn VBA in order to be able to handle it (as well as my planned career in economic research). I really appreciate your help and attention.
    Attached Files Attached Files
    Last edited by alepenn; 04-17-2011 at 11:04 PM.

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Columbia student exercise, Monthly > Quarterly data by Macro exercise

    Hi alepenn

    You've attached an Excel 2007 file (.xlsx) so I assume you're operating in an environment of Excel 2007 or above. Your profile indicates Excel 2003...if appropriate, please update your profile...it makes things easier if we know what version of Excel we're dealing with.

    To your issue.

    You CAN do this without macros using the Data --> Subtotal functions from the Excel 2007 Ribbon. However, you asked for a VBA solution and you ask for input.

    Using VBA, I'd create a new worksheet (I've called it "INF(output)". I'd then copy your Sheet "INF(start) to this new worksheet (this retains your original data). In the new worksheet, insert a new Column B. In that new Column, I'd place this formula "="Q"&LOOKUP(MONTH(A1),{1,4,7,10},{1,2,3,4})&"-"&TEXT(A1,"yy")" then copy it down. The attached code shows you how to do all of this. This formula places the quarter and year in Column B, eg (Q1 - 08 ).

    At this point, I'd use Excels built in Subtotal functions (albeit, in VBA, as you requested). Again, the code shows you how to do this. Your button is on Sheet INF(start).

    Step through the code so you can see whats happening...see this link to get a primer on debugging http://www.cpearson.com/excel/debug.htm

    Let me know how I can help. I'm still learning but am happy to let you know what I know.

    Complete code is in the attached. Let me know of issues.
    Attached Files Attached Files
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    04-26-2010
    Location
    london
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Columbia student exercise, Monthly > Quarterly data by Macro exercise

    Wow, awesome reply! I'm sorry for not replying sooner I didn't have a subscription set apparently, that's fixed now. Do you have a skype or something I could grill you (or another knowledgable member) on the line-by-line? As I noted this is more of an exercise in learning than anything else and I understand some of what is in there but I would genuinely appreciate the opportunity to go line-by-line with someone who might be willing to do so out of the goodness of their heart or for a paypal contribution to themselves or a charity of choice. Please PM me if you are willing.

    Thanks!

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,543

    Re: Columbia student exercise, Monthly > Quarterly data by Macro exercise

    Hi alepenn,

    Find the answer without needing any programming, using Pivot Tables.
    Group by Year and Quarter and display Averages instead of sums.

    I agree writing code is great, and I write my share, but finding the easiest way is more important.
    Attached Files Attached Files
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1