Solved

Macro in Excel

Posted on 2014-10-30
11
123 Views
Last Modified: 2014-11-04
Hello Experts,

I have a excel file as like below:

 Original File
I want to write a macro which will take the input from "E1" column i.e. 2014 and get the Quarter ,Month, week in the below manner :


Expected file
After making this it should delete the data from "E1" column.

For Week it should take only 7 days and continue like "Week 1" Week 2 .....and so on till 31st Dec of the year which is present on E1 column.  The month field will have the name of the month and should merge the cells of that months.

Attached here with the excel for how wanted to be done using macro.
Sample-File.xls
0
Comment
Question by:Swadhin Ray
  • 5
  • 4
  • 2
11 Comments
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40414596
Will all weeks be seven-day periods beginning with January 1-7 of the calendar year?  If so, where does December 31 go?  It is an extra day.  In leap years, Dec. 30 & 31 are extra.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40415173
Hello Ray,

Based on the year input in  "E1" column i.e. 2014 [as per my example or any other year ].
Here is the logic what I am looking for :

condition 1
1) check is the "E1" column is a leap year or not .
2) if yes the do not delete the "BL" column in first sheet.
  The assign the dates to the columns till "GD" column in first sheet
 Then assign the dates to the second sheet which will be similar to my first sheet .
3) else delete the  BL which is ( FEB 29th) as per the data in the first sheet.  
Once deleted then last column will be till "GC" , so now assign the dates till "GC" column in first sheet and put the dates into second sheet.  

condition 2
Assign weeks starting from  1st Jan accommodating  7 days a week....  

This is what I am looking for.

Here is the sample file with what I will get from the application always have two sheets 1 and 2:
Final-Sample.xls
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40415529
You can actually do this with formulas and conditional formatting rather than VBA.

Do you want to me provide an example or are you set on VBA route.

Thanks
Rob H
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Expert Comment

by:Rob Henson
ID: 40415572
Do the dates in row 6 have to be true dates so that you can use them in other calculations?

If not, then it doesn't matter what the value of E1 is as the dates with the formatting that you are showing will essentially not change. Therefore you could set it up with dates from any year, grouped and formatted as required and then leave it, except for an adjustment every four years to allow for 29 Feb; this could be a hidden column that gets unhidden every four years.

Thanks
Rob H
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40416311
Yes it should be true dates based on the input from E1 column. And I dont want to hide , I want to delete that column.
So that I can use it as a final report. The only option I am looking for VBA macro.
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40416457
Dates can easily be calculated from the input cell.

=DATE(E1,1,1)    will give 1 Jan of year specified.

Assuming 1 Jan in E6, the following in F6 will give 2 Jan

=E6+1   copy across row 6 will increment dates.

Week can then be determined by (row 6 date less 1 Jan)/7

Month:

=TEXT(E6,"mmm")    copied across.

Quarter, like week but divided by 91 (7 x 13).

With conditional formatting you can make the repeated values disappear. Don't think you can merge & centre though.
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40418928
Why I need VB because we have to make the formatting in second sheet too.
And also need to merge it.

So it would be great if you can provide me some sample for the given sheet.
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 40421472
thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40422050
Hi slobaray,

Since you've accepted a non-VBA solution for this question, please either post a new question specifically requesting a VBA solution or re-open this one.

VBA will be the only way to create/apply the merged cells for the weeks, months, and quarters

With Thanks,
-Glenn
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40422125
Correct me if I am wrong but I do not see that the formatting or merge settings will have to change once set up for the first time.

The dates will always start at 1 Jan so week 1 will always be the first 7 columns, week 2 the next 7 etc etc
Quarter 1 will always be the first 13 weeks, Quarter 2 the next 13 etc
Number of days in the Month will always be the same, except every 4 years when Feb has an extra day.

To overcome the Leap year issue, you could set up two copies of the required range and format with merge cells, one  accommodating 29 Feb and the other not. You can then just copy and paste formatting. One action required at start of Leap Year, one action required at start of next year and then leave alone for 3 years.

Thanks
Rob H
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 40423355
What I have done is I pulled the data as 29th Feb by default from my SQL which generates the data and put the year in E1 column .

Now what I did is checked if the year is a leap year or not , if not then delete the 29th day of feb. and then generate the dates , and then based on the dates generate the week, month ,quarter [Which will always start with 1 and from 1st JAN ], now accoumating till 7 days this will move on .,... .

Based on this I solved the problem. But I am still facing the issue on merging the cells in VBA code ,,.. Which I am still working on that.. But i know if I failed then I am sure I will open another question to get the help..

But the solution what was provided was also working only problem was the SQL which was generating the data was little hard coded for which I cannot use the provided solution here but in my another issue this was well utilized and we all are happy to use Expert exchange which holds the great experts together  :-) ....

Thanks a lot experts.
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question