Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Print incrementing date (2000/9.0.3821 SR1)

    I have a paper "form" that we've created in Excel. We've reserved space on the form to write in "today's date." Is it possible to use code or whatever to make Excel print a given date on the first form to print, then to print the following date on the second and so on...? We usually print these things in batches. When we start to run out of forms, we print maybe 20 or 30--enough to last a month or so. Of course, these incrementing dates will have to skip weekends and official holidays (Christmas, New Year's, etc.), plus any "plant holidays" (ours is a manufacturing site).

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

    Re: Print incrementing date (2000/9.0.3821 SR1)

    The macro below should do what you want, but will need to be modified to fit your workbook:

    1- The macro uses the WorkDay function from the Analysis ToolPak that comes with Excel. It is not automatically installed, so you will have to install it if you don't already have it installed.

    2- The macro assums that your form is on a worksheet named Form

    3- It assumes that you have entered a list of holidays in the Range A1:A10 on a worksheet named Holidays.

    4- It assums that the cell on the Form sheet where the date goes is cell A1.

    <pre>Public Sub PrintForms()
    Dim iForms As Integer, I As Integer, datToday As Date
    datToday = Date
    iForms = InputBox("Enter number of forms to print")
    For I = 0 To iForms - 1
    Worksheets("Form").Range("A1").Value = _
    "=WorkDay(DateValue(""" & datToday & """)," & I & ",Holidays!A1:A10)"
    Worksheets("Form").PrintOut
    Next I
    Worksheets("Form").Range("A1").Value = ""
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Well, Legare and sdckapr, all I can say is Wow! Thanks a bunch, guys! This is why I keep comin' back to da Lounge!

    I want to try both approaches when I get back to work in the morning. I'm not sure I've installed that ToolPak, but I guess I'll find out tomorrow. Allow me to ask for some clarification from you, Legare.

    Re: #2: May I assume that where your macro uses the word "Form," I should just substitute the name of my worksheet? Perhaps more important--do I need to use the full Windows file path? (We maintain this sheet on a shared network drive.)

    Re: #3: The worksheet called "Holidays"--must it be part of the aforementioned "Form"? That is, worksheet 2, for example? Can or should it be a separate file--say, on the computer hard drive as opposed to a shared directory?

    Shall I presume that the Analysis ToolPak automatically takes care of the weekends?

    Thanks again, both of you!

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

    Re: Print incrementing date (2000/9.0.3821 SR1)

    1- Yes, you need to replace all occurrances of "Form" with "yoursheetname". As long as the macro is in the same workbook as the worksheet, you do not need to use the full name with the path and filename, just the worksheet name.

    2- The macro is designed for there to be another worksheet in the same workbook with the "Form" worksheet and the macro that is named "Holidays". I figured that this would make it easy to update the holiday list for future years (you can put as many years of holidays in the list as you want as long as you adjust the range in the macro appropriately). This worksheet can be hidden if you don't want the users seeing it.
    Legare Coleman

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Thanks, Legare!

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Finally--after many other projects got in the way--I've tried your approach to fixing my problem. Now I've run into a "compile error." After I entered the code, I went to Tools > Macro... and saw my new macro "IncDate." I ran the macro and got the following message: "Compile Error. Statement invalid outside Type block." I click OK, and in the VBA window the line "DimSheetNum As Integer" is highlighted.

    Now what? Does this have anything to do with the fact that I've renamed the datasheet? The Project window shows my datasheet as "Sheet1 (am)" (Maybe I should mention that I actually have two forms to which I wish to apply this macro: Sheet1 [ "Sheet1 (am)" ] is a sign-in log for the morning hours, and Sheet2 [ "Sheet2 (pm)" ] is for the hours from 1 to 7 pm. So the worksheet tabs display the names "am" and "pm." I'd like to continue using these names. If this renaming is the source of the compile error, how do I re-work the macro?

    Thanks!

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print incrementing date (2000/9.0.3821 SR1)

    If the offending line really reads
    <pre>DimSheetNum As Integer</pre>

    then you should insert a space between Dim and SheetNum so that it reads
    <pre>Dim SheetNum As Integer</pre>


  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    G'morning, Legare: I'm trying your approach to this situation, and I've hit "Run-time error '9' : Subscript out of range." The input box ("Enter the number of forms to print") does appear. I enter a number, click OK...and then I get the run-time error.)

    I copied your code into the VBE, then modified it as follows:

    I've replaced "Form" with the name of my worksheet (see copy of my code below). (I made a copy of the original to monkey around with.)
    I've created an additional worksheet named Holidays, with a range of A1:A5 (two days for Thanksgiving, two for Christmas, and one for New Year's Day).
    The date does appear in cell A:1.

    So...where have I gone astray? My code:

    Public Sub PrintForms()
    Dim iForms As Integer, I As Integer, datToday As Date
    datToday = Date
    iForms = InputBox("Enter number of forms to print")
    For I = 0 To iForms - 1
    Worksheets("(copy) log sheets for East lab").Range("A1").Value = _
    "=WorkDay(DateValue(""" & datToday & """)," & I & ",Holidays!A1:A5)"
    Worksheets("(copy) log sheets for East lab").PrintOut
    Next I
    Worksheets("(copy) log sheets for East lab").Range("A1").Value = ""
    End Sub

    One more question: Your code in your post displays all the indentations one normally sees in VBA. When I was writing my reply just now, I used the space bar to create the indentations (the tab key wouldn't function). Yet in Preview mode, my reply lacked any indentations at all! What are you doing to make the code display properly here in the forum?

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

    Re: Print incrementing date (2000/9.0.3821 SR1)

    I copied the code in your message and pasted it into Excel, Created two sheets with the names in your code, and the macro ran with no problem. The error message you quoted usually means that you are trying to reference an object in a collection (like Worksheets) but have not correctly specified the name of the object. So my best guess as to why it did not work for you is that "(copy) log sheets for East lab" is not the exact name of the worksheet. When you get the message, you should be able to click on the Debug button and Excel will show you which line of code is causing the problem. If this does not help you find the problem, then can you post a copy of the workbook so I can look at it?

    The Lounge software will strip out all extra spaces and tabs you put into a message. There is a special set of tags call pre tags that you can put around text that you want displayed exactly as entered. If you put [ pre] (with no space between the [ and the p) before the code, and a [ /pre] (again with no space) after the code then the indention will be in the message. It would look like this:

    [ pre]
    <pre>Public Sub PrintForms()
    Dim iForms As Integer, I As Integer, datToday As Date
    datToday = Date
    iForms = InputBox("Enter number of forms to print")
    For I = 0 To iForms - 1
    Worksheets("(copy) log sheets for East lab").Range("A1").Value = _
    "=WorkDay(DateValue(""" & datToday & """)," & I & ",Holidays!A1:A5)"
    Worksheets("(copy) log sheets for East lab").PrintOut
    Next I
    Worksheets("(copy) log sheets for East lab").Range("A1").Value = ""
    End Sub
    </pre>

    [ /pre]
    Legare Coleman

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Doh! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Seems it almost always my own lack of attention to detail! Hans, I can always count on you to set me straight! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> The macro worked just fine, with one tiny problem: It fails to skip a holiday.

    Here's what happens: I run the macro, and it asks me first for the starting date and then for the number of sheets. To experiment, I chose to run sheets for the six-day period that starts with Wednesday, 27 Nov 02. The first date is a normal workday; the next two days (Thu and Fri) are holidays, followed by a two-day weekend and a then a normal workday Monday. Excel should have printed sheets for 27 Nov and 2 Dec only, but it also printed a sheet for Thu, 28 Nov. I've examined the code, but I can't see why it prints that extra sheet. My list of holidays is quite clear...or so it seems. Any ideas?

    Here's the code, by the way. (One more thing: I moved the cell address for the printed date to B1. The word "Date:" appears in A1.)

    Option Explicit
    Sub IncDate()
    Dim SheetDate As Date
    Dim SheetNum As Integer
    Dim x As Integer
    Dim i As Integer
    Dim j As Integer
    Dim DateAddress As String
    Dim isHoliday As Boolean
    Const NumHolidays = 5
    Dim HolidayList(NumHolidays) As Date
    HolidayList(0) = #11/28/2002#
    HolidayList(1) = #11/29/2002#
    HolidayList(2) = #12/24/2002#
    HolidayList(3) = #12/25/2002#
    HolidayList(4) = #1/1/2003#

    DateAddress = "$B$1"

    On Error Resume Next
    SheetDate = InputBox("What is the starting date?")
    Do Until Err.Number = 0
    Err.Clear
    SheetDate = InputBox("Try Again!" _
    & vbCrLf & "Invalid Date" _
    & vbCrLf & "What is the Starting Date?")

    Loop

    SheetNum = InputBox("How many sheets do you need?")
    Do Until SheetNum > 0
    SheetNum = InputBox("Try Again!" _
    & vbCrLf & "Invalid Number" _
    & vbCrLf & "How many sheets do you need?")

    Loop

    On Error GoTo 0
    i = 0

    For x = 1 To SheetNum
    isHoliday = False
    For j = 1 To NumHolidays
    If HolidayList(j) = SheetDate + i Then
    isHoliday = True
    End If
    Next j

    If Weekday(SheetDate + i) = vbSunday Or _
    Weekday(SheetDate + i) = vbSaturday Or _
    isHoliday Then
    i = i + 1
    Else
    Range(DateAddress).Value = Format(SheetDate + i, "d mmmm yyyy")
    ActiveSheet.PrintOut
    i = i + 1
    End If
    Next x
    End Sub

    Thanx, Hans et al!

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Legare: You are right on the money! I'm embarrassed to admit this, but "(copy) log sheets for East lab" is not the exact name of the worksheet. Ain't even close! You're talking workSHEETS and I'm thinking workBOOK. I was trying to use the file name, when what I should have used was simply the name of the worksheet. The name of the worksheet is--get this--"AM." Yeah...AM as opposed to PM--morning as opposed to afternoon. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    A-hem...! Well, I fixed that little situation and the macro runs. But there's another problem: You might have picked up on the fact that I changed the cell where the date will print from A1 to B1. Cell A1 now displays the word "Date." When I ran the macro, I told it to print six sheets. As I understand your macro, what I should have gotten was six sheets with the dates 30 Sep 02 through 7 Oct 02--skipping the weekend. But cell B1 displayed the error "#NAME?". I read the Excel Help page about "What does the error '#NAME?' mean," but I don't understand what it's trying to tell me.

    Here's the corrected code:

    <pre>
    Public Sub PrintForms()
    Dim iForms As Integer, I As Integer, datToday As Date
    datToday = Date
    iForms = InputBox("Enter number of forms to print")
    For I = 0 To iForms - 1
    Worksheets("AM").Range("B1").Value = _
    "=WorkDay(DateValue(""" & datToday & """)," & I & ",Holidays!A1:A5)"
    Worksheets("AM").PrintOut
    Next I
    Worksheets("AM").Range("B1").Value = ""
    End Sub

    </pre>



    Thanks for the tip on that "[pre]" stuff, by the way!

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

    Re: Print incrementing date (2000/9.0.3821 SR1)

    That most likely goes back to my comment #1 in my first message:

    <hr>1- The macro uses the WorkDay function from the Analysis ToolPak that comes with Excel. It is not automatically installed, so you will have to install it if you don't already have it installed.

    <hr>

    That is the error you will get if the Analysis ToolPak is not installed and activated.

    First, go to the Tools menu and select Addins... Look through the list and see if Analysis ToolPak is in the list. If it is, then click in the check box to check that like and then click on OK. That should activate the Analysis TookPak addin. If Analysis ToolPak is not in the list, you are going to have to go back to the Excel install Setup and install the Analysis ToolPak and then activate it.
    Legare Coleman

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Print incrementing date (2000/9.0.3821 SR1)

    It's another detail. Your HolidayList array starts with index 0:

    <font face="Georgia">HolidayList(0) = #11/28/2002#</font face=georgia>

    but your check starts with index 1:

    <font face="Georgia">For j = 1 To NumHolidays</font face=georgia>

    So HolidayList(0) is never actually checked against...

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Bravo! That's it! <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    I had picked up on your reference to the Analysis ToolPak, and yesterday I made a point of installing it before I even got started with this project. However, I had not checked its box in the list of add-ins.

    Thanks a million!

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Print incrementing date (2000/9.0.3821 SR1)

    Aha! Thank you again, Hans! I just changed the code to read:

    For j = 0 To NumHolidays

    and now it works correctly! <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

Page 1 of 2 12 LastLast

Posting Permissions

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