Solved

Macro in Excel

Posted on 2014-10-30
11
109 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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 31

Accepted Solution

by:
Rob Henson earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0
 
LVL 27

Expert Comment

by:Glenn Ray
Comment Utility
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 31

Expert Comment

by:Rob Henson
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

771 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now