1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Macro with Date logic

Discussion in 'Business Applications' started by mamcelr, Feb 12, 2015.

Thread Status:
Not open for further replies.
Advertisement
  1. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Within the attached file is a Macro (start with Ctrl-Z) designed to add two columns after Column "B", then calculate in Column C the $ difference and in Column D the % difference between a static column that represents year to date data and a dynamic column that represents year end data from the prior year. In this case, dynamic means that this particular column changes in terms of it's position once every month. That happens on the first day of the month when a new daily column is added to the left of the just ended monthly column.

    As an example, the data is organized newest to oldest, right to left. So in January the two columns that are used to calculate $ and % are "E" and "F". In February, "E" and "G", in March "E" and "H" and so on.

    Every month I need to update the macro to account for the addition of a new column. My question is, can the Macro be enhanced to account for the addition of the new column so that I never have to update it?

    The attached file has two tabs, one for January and one for February data. The Macro is setup to run against the February data. It will not work on January because I made edits to look at the columns as they would appear for the month of February. Within the Macro, I have noted the two lines that require editing every month.

    Thank you in advance for any help/advice you may have . . .



    Tech Support Guy System Info Utility version 1.0.0.2
    OS Version: Microsoft Windows 7 Enterprise, Service Pack 1, 64 bit
    Processor: Intel(R) Core(TM) i5-3340M CPU @ 2.70GHz, Intel64 Family 6 Model 58 Stepping 9
    Processor Count: 4
    RAM: 4037 Mb
    Graphics Card: NVIDIA NVS 5200M, 1024 Mb
    Hard Drives: C: Total - 305242 MB, Free - 247818 MB;
    Motherboard: Dell Inc., 0H3MT5
    Antivirus: McAfee VirusScan Enterprise, Updated and Enabled
     

    Attached Files:

  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, Let me see if I understand your problem.
    If I run your YTD maco it adds / copies two new columns

    The sheet '02-11-15 Totals' starts with three columns C, D and E

    After the macro runs the columns are shifted right and two new C and D are added with a $ and a % sign

    What is it you require, I do not quite understand it
     
  3. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Thank you for checking in. Your description of what the Macro does is absolutely correct.

    Here's what I'm trying to solve for. Every month I need to update the Macro manually because the system generating the report adds a column of information after every month-end. If you look at both tabs, you'll see the change. Two columns of information for January’s tab, then January ends, January's ending balances are fixed and a new column is added for February, making a total of 3 columns; year-end 2014, January month-end and February's daily information. When February ends, its month-end totals will be fixed and a 4th column added for March.

    Because the calculation in the macro always uses year-end 2014 and that column is being pushed to the right at the end of every month, I manually need to change calculation to account for the addition of a new column as shown below. I've put a note in the Macro (in green type) to remind myself of what needs to be updated. The following is copied directly from the Macro:

    '1st day of new month change 2nd RC to current month number plus 2; i.e.February=2+2="RC[4]"
    ActiveCell.FormulaR1C1 = "=RC[2]-RC[4]"

    ' 1st day of new month change 2nd and 3rd RC to current month number plus 1; i.e. February=2+1="RC[3]"
    ActiveCell.FormulaR1C1 = "=(RC[1]-RC[3])/RC[3]"

    My hope is that someone can help me improve the Macro so no edit is required. That would require the Macro taking into account the current date and knowing that because it's a day in the month of February that the calculations would be run against RC[3] as opposed to a day in the month of March when the calculation would be run against RC[4]. These two columns, RC[3] in February and RC[4] in March, both represent the location of the 2014 year-end information after the system adds a new column for the new month.

    Hope that helps clarify my dilemma . . .
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Let me see if I understand.
    The daily column in the case of worksheet 02-11-15 Totals,
    1. Will this daily column be overwritten every time?
    2. Once you reach the end of that month this column stays and a new one is automatically placed in front of this column moving Column E (Year end 2014) to the right one column? Correct?
    3. The calculation at every update is that always against the end of the previous month r the end of the year 2014?

    You've got two macros named YTD so this does not shed any light for me
     
  5. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Hans,

    Question #1 & #2: yes, correct in both cases.
    Question #3: Everyday the calculation is the same, the most current daily information against year-end 2014. The current daily information is always Column E, after the Macro inserts the two new columns. The year-end 2014 is only constant in its Column for one month then gets moved to the right because the daily information for the last day of the month becomes a new permanent column.

    Hans . . . I've only been a member since 2011 and have only sought help 3-4 times. I just realized you helped me with my very first question, the most complex problem I've ever had and I still use your solution to this day!

    Appreciate the help . . . again!

    Mac
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi, Found it, Change in Balance... :)
    Another verification question.
    Your daily value will always be Column E and on a new month a 'new Column E' is inserted and the two calculations are refreshed with the new value against the end of year which also moved up one column.

    This column with the daily value you will fill it in and you just want the macro to recalculate Columns C and D.
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I wrote the following macro, using the references from your own recorded macros and just adding some 'intelligence'
    This is the full code to update Columns C and D, NOT INSERT COLUMNS C and D
    I am assuming that columns C and D are already present

    Code:
    Sub Recalculate()
    '*  Assuming that Columns C and D already exist
    Dim tCol    As Long
    Dim eCol    As Long
    Dim xRow    As Long
    Dim lstRow  As Long
    Dim lstCol  As Long
    eCol = Cells(4, Columns.Count).End(xlToLeft).Column
    tCol = 5
    lstRow = WorksheetFunction.Max(8, Cells(Rows.Count, eCol).End(xlUp).Row)
    If eCol <= 5 Then Exit Sub      '*  this means that there is only one column or no columns after column D
    For xRow = 8 To lstRow
        If Len(Trim(Cells(xRow, eCol).Value)) > 0 Then
            With Range("C" & xRow)
                .Formula = "=RC[2]-RC[" & eCol - 3 & "]"
                .Font.Color = -65536
                .Font.TintAndShade = 0
                If xRow >= 27 And xRow <= 40 Then
                    .Interior.Pattern = xlSolid
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.ThemeColor = xlThemeColorAccent6
                    .Interior.TintAndShade = 0.799981688894314
                    .Interior.PatternTintAndShade = 0
                End If
            End With
        
            With Range("D" & xRow)
                If Cells(xRow, eCol).Value <> 0 Then
                    .Formula = "=(RC[1]-RC[" & eCol - 4 & "])/RC[" & eCol - 4 & "]"
                Else
                    .Value = 0
                End If
                .Font.Color = -65536
                .Font.TintAndShade = 0
                .Style = "Percent"
                .NumberFormat = "0.0%"
                If xRow >= 27 And xRow <= 40 Then
                    .Interior.Pattern = xlSolid
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.ThemeColor = xlThemeColorAccent6
                    .Interior.TintAndShade = 0.799981688894314
                    .Interior.PatternTintAndShade = 0
                End If
            End With
        End If
    Next xRow
    Range("C:D").ColumnWidth = 11
    Range("A1").Select
    End Sub
    
    Paste this macro in you VBA project and run it.
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I rewrote my macro and added the option to check and see if columns C and D are not already present.
    If you update the February Column and press the button, the data will update.

    I've attached my version in the zip file.
     

    Attached Files:

  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Did you get the time to take a look?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I imagine you're busy but it would be nice to have a little feedback :)
     
  11. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Hans,

    My apologies . . . I was traveling all last week and returned Friday to a mess. Still working here over the weekend. I am going to work on this today. I'll be back with you before end of day. Again, my apologies for going dark.

    Mac
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    No problem, I was just curious :)
     
  13. mamcelr

    mamcelr Thread Starter

    Joined:
    Oct 24, 2011
    Messages:
    39
    Hans . . . please see attached. I had a problem embedding a picture in my response and decided just to put it all in one file.
     

    Attached Files:

  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Well, that's clear, I assumed that you would start a new file every new year and have the last entry for the previous year always be the last column. This (of course, I do not know how you're working with it) seemed the most logical way for me.
    The code of the macro will need to be more complicated since you will have to add a search routine to locate the last entry for the previous year, and this will vary every time.
    You'll be able to fit in 1300 years in one sheet, but it won't make it easier.
    You could even think of a new worksheet for every year and the last entry per year is cariied over to the next sheet.
    I'll see if I can write soem simple code for the 'moving' year's end as you have it now.
    You see, information is evrything and assumptions as I did is the greatest trap when it comes to this type of solutions and help:)
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Try this one, the attachment's name is the same but I addede an extra function to locate the previous year's column.

    I think it will work, I do not know if it works when starting from scratch
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1142980

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice