Tags: basically, collect, date, excel, form, fun, increment, macro, microsoft, msdn, rolleyes, software, sorts

Trying to increment by one day in a macro

On Microsoft » Microsoft Excel

8,203 words with 3 Comments; publish: Fri, 23 May 2008 06:14:00 GMT; (30678.13, « »)

Hello,

Having all sorts of fun getting this one worked out, to date, n

success at all :rolleyes:

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?action=getinfo&userid=1286

View this thread: http://www.excelforum.com/showthread.php?threadid=31897

All Comments

Leave a comment...

  • 3 Comments
    • 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 :rolleyes:

      > 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?action=getinfo&userid=12866

      > View this thread: http://www.excelforum.com/showthread.php?threadid=318973

      --

      Dave Peterson

      #1; Fri, 23 May 2008 06:15:00 GMT
    • 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

      #2; Fri, 23 May 2008 06:16:00 GMT
    • 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?action=getinfo&userid=12866

      > View this thread: http://www.excelforum.com/showthread.php?threadid=318973

      --

      Dave Peterson

      #3; Fri, 23 May 2008 06:17:00 GMT