Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to increment by one day in a macro
Hello, Having all sorts of fun getting this one worked out, to date, n success at all I basically one to have a form to collect the start date and nr of day required then print the worksheet with the dates incrementing from th start date by one for the number of days required. This is what I have tried: Private Sub cmdPrint_Click() 'Dim Variables Dim D As Integer Sheets("LogSheet").Select Range("Date") = "" 'Transfer Info from frmDriverLogInfo Sheets("LogSheet").Select Range("Date") = txtStartDate.Value D = cboNrOfDays.Value 'Print Selected Number of Days For I = 1 To D Sheets("LogSheet").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveSheet.Range("Date").Value = ActiveSheet.Range("Date").Value 1 Next I End Sub Any help would be most appreciated. Many thanks, Phi -- Phil ----------------------------------------------------------------------- PhilB's Profile: http://www.excelforum.com/member.php...fo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=31897 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to increment by one day in a macro
Your code worked for me--after I dimmed I--and if I put valid entries in each
choice. I'm guessing that you were typing something in that excel couldn't see as a date. Option Explicit Private Sub cmdPrint_Click() 'Dim Variables Dim D As Long Dim I As Long With Sheets("LogSheet") If IsDate(Me.txtStartDate.Value) Then .Range("Date") = CDate(Me.txtStartDate.Value) Else MsgBox "Please enter a date!" Exit Sub End If D = 0 If IsNumeric(Me.cboNrOfDays.Value) Then D = CLng(Me.cboNrOfDays.Value) End If If D = 0 Then MsgBox "Please enter the number of days" Exit Sub End If 'Print Selected Number of Days For I = 1 To D .PrintOut preview:=True, Copies:=1, Collate:=True .Range("Date").Value = .Range("Date").Value + 1 Next I End With End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.cboNrOfDays .Clear For iCtr = 1 To 5 .AddItem iCtr Next iCtr End With End Sub (I added the preview:=true to save some trees while testing.) PhilB wrote: Hello, Having all sorts of fun getting this one worked out, to date, no success at all I basically one to have a form to collect the start date and nr of days required then print the worksheet with the dates incrementing from the start date by one for the number of days required. This is what I have tried: Private Sub cmdPrint_Click() 'Dim Variables Dim D As Integer Sheets("LogSheet").Select Range("Date") = "" 'Transfer Info from frmDriverLogInfo Sheets("LogSheet").Select Range("Date") = txtStartDate.Value D = cboNrOfDays.Value 'Print Selected Number of Days For I = 1 To D Sheets("LogSheet").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True ActiveSheet.Range("Date").Value = ActiveSheet.Range("Date").Value + 1 Next I End Sub Any help would be most appreciated. Many thanks, Phil -- PhilB ------------------------------------------------------------------------ PhilB's Profile: http://www.excelforum.com/member.php...o&userid=12866 View this thread: http://www.excelforum.com/showthread...hreadid=318973 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to increment by one day in a macro
Dave, This is what I ended up, borrowing the code you supplied an transferring the 'date' range to another worksheet. I guessed that i may have been an issue with formatting the date on the printed shee that was causing the problem as I was trying to seperate d/mmm/yyy into three seperate cells to prevent the ### problem in columns tha were fitting the other information. Private Sub cmdPrint_Click() 'Dim Variables Dim D As Long Dim I As Long With Sheets("DataSheet") If IsDate(Me.txtStartDate.Value) Then Sheets("DataSheet").Range("date") = CDate(Me.txtStartDate.Value) Else MsgBox "Please enter a date!" Exit Sub End If D = 0 If IsNumeric(Me.cboNrOfDays.Value) Then D = CLng(Me.cboNrOfDays.Value) End If If D = 0 Then MsgBox "Please enter the number of days" Exit Sub End If 'Unload Form Unload frmDriverLogInfo 'Print Selected Number of Days For I = 1 To D 'Change PrintOut to PrintOut preview = true for testing Sheets("LogSheet").PrintOut Copies:=1, Collate:=True Sheets("DataSheet").Range("date").Value Sheets("DataSheet").Range("date").Value + 1 Next I End With Sheets("DataSheet").Range("date").Value Sheets("DataSheet").Range("date").Value - 1 For Each w In Application.Workbooks w.Save Next w Application.Quit End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.cboNrOfDays .Clear For iCtr = 1 To 7 .AddItem iCtr Next iCtr End With End Sub I tacked the -1 day on the end to save it at the last day printed. Many thanks for all your help, it certainly made the problem les problematic. Take care, Phi -- Phil ----------------------------------------------------------------------- PhilB's Profile: http://www.excelforum.com/member.php...fo&userid=1286 View this thread: http://www.excelforum.com/showthread.php?threadid=31897 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to increment by one day in a macro
Getting a date from a Textbox or inputbox can be a problem.
You may want to consider adding a calendar control to your form. (It'll look pretty neat, too!) Ron de Bruin has some samples at: http://www.rondebruin.nl/calendar.htm Ron has a link (at the bottom of that URL) where you can get a free version of a control. (It's not included in excel (but if you have it, you can use it there.) (I think it's installed with Access.) PhilB wrote: Dave, This is what I ended up, borrowing the code you supplied and transferring the 'date' range to another worksheet. I guessed that it may have been an issue with formatting the date on the printed sheet that was causing the problem as I was trying to seperate d/mmm/yyyy into three seperate cells to prevent the ### problem in columns that were fitting the other information. Private Sub cmdPrint_Click() 'Dim Variables Dim D As Long Dim I As Long With Sheets("DataSheet") If IsDate(Me.txtStartDate.Value) Then Sheets("DataSheet").Range("date") = CDate(Me.txtStartDate.Value) Else MsgBox "Please enter a date!" Exit Sub End If D = 0 If IsNumeric(Me.cboNrOfDays.Value) Then D = CLng(Me.cboNrOfDays.Value) End If If D = 0 Then MsgBox "Please enter the number of days" Exit Sub End If 'Unload Form Unload frmDriverLogInfo 'Print Selected Number of Days For I = 1 To D 'Change PrintOut to PrintOut preview = true for testing Sheets("LogSheet").PrintOut Copies:=1, Collate:=True Sheets("DataSheet").Range("date").Value = Sheets("DataSheet").Range("date").Value + 1 Next I End With Sheets("DataSheet").Range("date").Value = Sheets("DataSheet").Range("date").Value - 1 For Each w In Application.Workbooks w.Save Next w Application.Quit End Sub Private Sub UserForm_Initialize() Dim iCtr As Long With Me.cboNrOfDays Clear For iCtr = 1 To 7 AddItem iCtr Next iCtr End With End Sub I tacked the -1 day on the end to save it at the last day printed. Many thanks for all your help, it certainly made the problem less problematic. Take care, Phil -- PhilB ------------------------------------------------------------------------ PhilB's Profile: http://www.excelforum.com/member.php...o&userid=12866 View this thread: http://www.excelforum.com/showthread...hreadid=318973 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro - How to increment cell reference by one row | Excel Worksheet Functions | |||
Using macro recorder increment cell by one? | Excel Discussion (Misc queries) | |||
copy cell with macro and increment down each time | Excel Worksheet Functions | |||
Macro to increment invoice number | Excel Programming | |||
Macro to Increment a Cell's Value By 1 | Excel Programming |