Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Excel - Need help urgently (Offcie 2K)

    Hi, i'm trying to make a sheet in Excel that shows the current date, and the future dates for about the next 3 months so that people can update the sheet but i have absolutely no idea how to do this, does anyone have any idea at all as i have not found anything else on the net, please please please help.

    Many Regards in Advance

    Phil

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Excel - Need help urgently (Offcie 2K)

    I am not quite sure what you are after, but if you just want to have todays date in a cell use the =today() function and for future dates just add days to it for as many as you need. So =today()+7 is a week in the future.
    If you want the date in a module then use the Date funtion, myDate = Date and again for future dates just add the days to it myDate = Date + 7

    HTH, if not please give more details of what you need.

    Peter

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Excel - Need help urgently (Offcie 2K)

    Peter,

    Yes, that does sound like what i need, but i need the dates accross the top of the spreadsheet, then say i added some information for a month in advance i want that to slowly work it's way accross towards todays date (a bit like a calender), hope thats of more information, i'm very new to VBA, and have been given a project to do in a very short time!

    Regards

    Phil

  4. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Excel - Need help urgently (Offcie 2K)

    John Walkenbach has some books that might help.
    See http://www.j-walk.com/ss/about/index.htm.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Excel - Need help urgently (Offcie 2K)

    Phil, is the attached workbook what you had in mind? You can view the code by pressing <Alt>+<F11> and double-clicking on ThisWorkbook, if necessary.

    I used the Workbook Open event to freeze the panes and find the current date. Here is the code.
    <pre>Option Explicit

    Private Sub Workbook_Open()
    Worksheets("Sheet1").Activate
    ActiveWindow.FreezePanes = False
    Range("B2").Select
    ActiveWindow.FreezePanes = True
    Rows(1).NumberFormat = ("0.00")
    Cells.Find(what:=CSng(Int(CSng(Now))), LookIn:=xlValues).Select
    Rows(1).NumberFormat = "d-mmm-yy"
    End Sub</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VBA Excel - Need help urgently (Offcie 2K)

    The attached workbook in the previous post does not work correctly for me using IE 6.0. Todays cell is selected, but the window does not scroll to it. However if you save the workbook to a file and then open it in Excel, it should work. Let me know if not. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Excel - Need help urgently (Offcie 2K)

    Try the following:

    1- In the cell where you want today's date enter the formula:

    <pre>=TODAY()
    </pre>


    2- In the cell where you want tomorrow's date enter the formula:

    <pre>=A1+1
    </pre>


    where A1 is replaced with the cell address of the cell used in step 1.

    3- Select the cell used in step 2 above. Right click on the fill handle in the lower right corner of the cell and drag the cell over the number of cells where you want the successivly higher days.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •