• 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.

Macro to evaluate date range AND copy/paste corresponding cell

M

mrbilly

New Member
Afternoon All.


New to VBA, teaching myself and what I'm trying to do has left me stumped - would be interedted to know how it can be achieved (if possible). All help is greatly appreciated!


I have 3 columns; a start date, an end date and an event. Now sometimes the start and end will be the same, i.e. the event lasts one day, sometimes it goes into a second or third day and so the start and end dates will differ.


The first part of my macro evaluates the dates and returns the start date and (if applicable) all dates up to and including the end date. This new list should be returned in column d.


The second part of my macro should then copy the 'event' to every returned value in column d into the corresponding cell in column e.


So, for example, if 'Camping Trip' started on the 1st and finished on the 3rd columns D & F would read:


1st January Camping Trip

2nd January Camping Trip

3rd January Camping Trip


I've managed to sort out the first part of the problem but having real difficulty incorporating a copy/paste function into the macro - is it even possible?


Again, I'm new to VBA so apologies if asking an obvious question, or if I have used incorrect terminology. Any comments, feedback and/or suggestions will be gratefully received.


mrbilly
 
Hi, mrbilly!


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...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


Despite of this, consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you.


Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!
 
Hi mrbilly,


As you already got welcome kit from our great SirJ(James)B(Bond)(00)7 (of excel), please follow forum rules in order to help us serve you better.


According to your example above, I have tried to written the code to arrange the data in the same fashion: Below here is the code:


Sub DataArrange()


Application.ScreenUpdating = False


'Loop through the start date


For i = 2 To Sheet1.Range("A" & Rows.Count).End(xlUp).Row


'store the start date teporarily


TempVal = Sheet1.Range("A" & (i)).Value


'get the difference of start date and end date


lngNoOfDays = DateDiff("d", Sheet1.Range("A" & (i)).Value, Sheet1.Range("B" & (i)).Value)


'Get all the dates between start and end date and list them in col D

'List the event name of those dates in Col E


For counter = 0 To lngNoOfDays


DatLstRow = Sheet1.Range("D" & Rows.Count).End(xlUp).Row + 1

Sheet1.Range("D" & (DatLstRow)).Value = TempVal + counter

Sheet1.Range("E" & (DatLstRow)).Value = Sheet1.Range("C" & (i)).Value

Next


Next


Application.ScreenUpdating = True


End Sub


For e.g., say you have data as follows from Col A to C


start date>>>> end date>>>>> events

11/12/2012>>>> 11/15/2012>>>>> event1

11/14/2012>>>> 11/20/2012>>>>> event2

11/15/2012>>>> 11/16/2012>>>>> event3


The code will returns the start date and all dates up to and including the end date(at Col D) and the event's name(at Col E) as follows:


11/12/2012>>>>> event1

11/13/2012>>>>> event1

11/14/2012>>>>> event1

11/15/2012>>>>> event1

11/14/2012>>>>> event2

11/15/2012>>>>> event2

11/16/2012>>>>> event2

11/17/2012>>>>> event2

11/18/2012>>>>> event2

11/19/2012>>>>> event2

11/20/2012>>>>> event2

11/15/2012>>>>> event3

11/16/2012>>>>> event3


I have also uploaded the workbook here:


http://speedy.sh/8d9d2/Data-Arrangement.xlsm


PS:Click the button in the sheet to get the data arranged.


In case, this is not what you are looking for, plz consider uploading a sample workbook.


Regards,

Kaushik
 
Wow. You're an absolute legend Kaushik; exactly what I was looking for. I hadn't realised you could store information on a temporary basis - definitely something I shall learn more about.


And thank you for the welcome SirJB7 - most accomodating!
 
Hi mrbilly,


Glad to help you dear...come back whenever needed...


And appreciate your kind words...


Best Regards,

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