+ Reply to Thread
Results 1 to 20 of 20

Macro to show upcoming date

  1. #1
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Macro to show upcoming date

    I have a spread sheet with dates running across the columns .e.g H11 = 01/09/12 H12 02/09/12 etc.
    The columns have a variety of upcoming events
    The cell which corresponds with the event and the date is marked with a letter 'e'

    What I'm looking for is that in the G column the most upcoming date for the event is shown e.g. with the event 'Breakfast' which happens once a month I want it the cell in column G to say the next due date e.g. 03/04/12, but once that date has passed I want it to show April's date e.g. 07/04/12 (corresponding to the marked event dates)

    Hope that is clear

    Thanks

  2. #2
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    Why use VBA for this? Why not just use a vlookup? For example, if the event "Breakfast" has dates April 3, 2012, May 4 2012 and June 2 2012, create a lookup table:

    Column A
    1: 3/4/2012
    2: 4/5/2012
    3: 2/6/2012

    Then use a vlookup: "=VLOOKUP(NOW(), $A$1:$A$3, 1)"

  3. #3
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    Sorry, my formula will show the most recent date. for the upcoming date use:

    "=INDEX($A$1:$A$3, MATCH(NOW(), $A$1:$A$3,1)+1)"

  4. #4
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Attachment 155030

    The above image shows the layout. I don't know if your suggestion would work.
    As you can see an E marks that on the 12th of May there is a breakfast event, I was looking for a way that the Earliest Date cell shows what the next upcoming date is for the event row.

    Thanks

  5. #5
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    your attachment didn't come through. Why not just attach the excel file?

  6. #6
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    test.xlsm

    File should be attached.

  7. #7
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    See attached file. I don't think you need a macro.

    test.xlsx

  8. #8
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Fantastic, thank you.

  9. #9
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Sorry about this addition, looking back I wasn't that clear. Is there a way that the formula can take into account the current date and thus skip over previous event dates? e.g. although a Men's Breakfast was marked for the 2nd of May now that the date has passed I'd like the column to show the June date.

    Thanks

  10. #10
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Does anyone know if I can modify the formula to take into account the current date and so skip over recording the 'earliest date' that is now in the past, or would I require code?

    Thanks

  11. #11
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    Ok, my second attempt. Notice that I have defined a dynamic range called "Future". Go to the Name Manager to see the definition. Other than that, the formulas are straightforward.

    Date Lookup.xlsx

  12. #12
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    That's exactly what I'm looking for, thanks! I'm just having a few problems transferring the formula across to the workbook which runs macros for the other things it does. I have made sure that I've defined the name in the new sheet but I'm getting an N/A appear even when date is highlighted.

  13. #13
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    OK, I give up. I've been trying to avoid VBA, but it hasn't worked. Here's a function that should do the trick. Also, here's the workbook.

    DateLookup.xlsm

    Please Login or Register  to view this content.

  14. #14
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Both methods you offered were genius, the problem was at my end. Stupid mistake on my behalf I tried transferring code to the latest updated sheet I've made however that sheet begins in Sept '12. Both the formula and the code were throwing a wobbly because everything was in the future and I guess it needs the calendar dates to at least start begin in the past for it to work. That's perfectly fine because I wouldn't be using the spreadsheet until September had begun so there would be no issue.

    Thank you once again for your help on this matter it has been greatly appreciated.

  15. #15
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Just a query regarding the above code. Is it easy to modify so the code would run with the letter "P" AND the letter "E"? If it would not be easy and would require a massive rewrite don't worry about it, it was just an idea for an additional feature of spreadsheet to note provisional events.

    Thanks

  16. #16
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    Probably the easiest way is just to piggy-back on the existing code, as below. Leave the EventLookup code unchanged, and use a new function to return the minimum (earliest date). For the other solution, the dynamic range solution, you can use an "IFERROR" statement within a cell. Come to think of it, an "IFERROR" statment would also work for the VBA solution, as in '=IFERROR(MIN(EVENTLOOKUP(DateRange, EventRange, "E"), EVENTLOOKUP(DateRange, EventRange, "P")), IFERROR(EVENTLOOKUP(DateRange, EventRange, "E"), EVENTLOOKUP(DateRange, EventRange, "P")))'. Cumbersome, yes, but it should work.

    The code below should be easily modified to handle more than just E and P.

    Please Login or Register  to view this content.

  17. #17
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    I'm possibly being a bit dense, but I can't seem to get it to work. I've tried adding the code after previous code and adding it as a different module but it doesn't seem to recognize P, then when seeking to use "IFERROR" statement I just get a "VALUE" error. I'm attaching copy of the document with code added after previous event look up code.

    Thanks

    Paul
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    I've had another go but can't seem to see what's not working. Any idea?

    Thanks

  19. #19
    Registered User
    Join Date
    05-09-2012
    Location
    Des Moines, IA
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Macro to show upcoming date

    OK, I tried again. You were right, the last one didn't work.

    I've included a copy of the worksheet with the events stripped out; the events were causing problems for my version of excel.

    Note that there are two ways to use the function. One is to enter an array of possible values in curly braces, the other is to refer to a range of values.

    Copy of Planogram 2012-2013.xlsm

    Please Login or Register  to view this content.

  20. #20
    Registered User
    Join Date
    05-09-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    22

    Re: Macro to show upcoming date

    Brilliant, thank you very much.

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.09802 seconds
  • Memory Usage 9,313KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (3)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (20)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (20)postbit_legacy
  • (20)postbit_onlinestatus
  • (20)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1