DaveGib
03-11-2014, 01:59 AM
Hi All,
I have the code below that works,...... but very s l o w l y!!
Can someone please guide me as to how to improve it?
What I have is a simple user form that the user puts a start date, and an end date.
Using this info I Have entered the start date in column F row 7, I have done a calculation of number of days between dates. using this I have added a day to the initial date in a new column, for the number of days stored. i.e. if the first date is 11/3/2014 and last date is 21/3/2014 I am trying to create a series of dates in the same row that corresponds with the input dates as Column headers. This is done with the first loop.
With the second loop, I am inserting a formula above each date that will give the day of the week for the date i.e. above the date 11/3/2014 will show "Tue"
As I say this is all working but not very efficiently!!
Thanks in advance for taking the time to read this..........
Dave
Private Sub CBOK_Click()
Dim s As Date
Dim e As Date
Dim d As Integer
Dim i As Integer
Dim j As Integer
s = TextBox1.Value ' 1st Date from TextBox1 Input by user
e = TextBox2.Value ' End Date fom textBox2 Input by user
d = Int(e - s) + 6 ' Number of columns to create, starting at Col 6
Range("F7").Select ' put 1st Date as entered by user in Cell F7
Selection.Value = s
For j = 6 To d
For i = 7 To d ' put subsequent dates, to end date in same row
Cells(7, i).Value = Cells(7, i - 1) + 1
Cells(7, i).Select
With Selection ' centre date in cell
.HorizontalAlignment = xlCenter
End With
' Put the day of the week above the dates entered
Cells(6, j).FormulaR1C1 = _
"=CHOOSE(WEEKDAY(R[1]C),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri"",""Sat"")"
Cells(6, j).Select
With Selection ' centre day in cell
.HorizontalAlignment = xlCenter
End With
Next i
Next j
Unload Me
End Sub
I have the code below that works,...... but very s l o w l y!!
Can someone please guide me as to how to improve it?
What I have is a simple user form that the user puts a start date, and an end date.
Using this info I Have entered the start date in column F row 7, I have done a calculation of number of days between dates. using this I have added a day to the initial date in a new column, for the number of days stored. i.e. if the first date is 11/3/2014 and last date is 21/3/2014 I am trying to create a series of dates in the same row that corresponds with the input dates as Column headers. This is done with the first loop.
With the second loop, I am inserting a formula above each date that will give the day of the week for the date i.e. above the date 11/3/2014 will show "Tue"
As I say this is all working but not very efficiently!!
Thanks in advance for taking the time to read this..........
Dave
Private Sub CBOK_Click()
Dim s As Date
Dim e As Date
Dim d As Integer
Dim i As Integer
Dim j As Integer
s = TextBox1.Value ' 1st Date from TextBox1 Input by user
e = TextBox2.Value ' End Date fom textBox2 Input by user
d = Int(e - s) + 6 ' Number of columns to create, starting at Col 6
Range("F7").Select ' put 1st Date as entered by user in Cell F7
Selection.Value = s
For j = 6 To d
For i = 7 To d ' put subsequent dates, to end date in same row
Cells(7, i).Value = Cells(7, i - 1) + 1
Cells(7, i).Select
With Selection ' centre date in cell
.HorizontalAlignment = xlCenter
End With
' Put the day of the week above the dates entered
Cells(6, j).FormulaR1C1 = _
"=CHOOSE(WEEKDAY(R[1]C),""Sun"",""Mon"",""Tue"",""Wed"",""Thu"",""Fri"",""Sat"")"
Cells(6, j).Select
With Selection ' centre day in cell
.HorizontalAlignment = xlCenter
End With
Next i
Next j
Unload Me
End Sub