My problem is pretty straightforward: I have a UserForm where the user insert two dates, the start_date and the end_date.
When the OK button (btnOK) is pressed, I want the column A populated from the start_date to the end_date.
This is the code:
Sub btnOK_Click()
Dim myDate as Date
Dim start_date as Date
Dim end_date as Date
Dim k, cont as Long
start_date = Format(textStart.Value, "dd/MM/yyyy")
end_date = Format(textEnd.Value, "dd/MM/yyyy")
myDate = Format(start_date, "dd/MM/yyyy")
k = 0
cont = 1 ' Every 7 days i want 2 rows-space
Range("A1").Select
Do Until myDate = end_date + 1
Selection.Offset(k, 0).Select
Selection.Value = Format(myDate, "dd/MM/yyyy")
myDate = format(myDate + 1, "dd/MM/yyyy")
k = 0
If cont = 7 Or cont Mod 7 = 0 Then
k = k + 3
Else
k = k + 1
End If
cont = cont + 1
Loop
End Sub
So, the code works fine, the only problem I have is some dates format. A little example here:
' Italian formatting
start_date = "30/09/2024"
end_date = "10/10/2024"
| A | B | C |
|---|---|---|
| 30/09/2024 | Mon | dd/MM/yyyy |
| 10/01/2024 | Tue | MM/dd/yyyy |
| 10/02/2024 | Wed | MM/dd/yyyy |
| 10/03/2024 | Thu | MM/dd/yyyy |
| 10/04/2024 | Fri | MM/dd/yyyy |
| 10/05/2024 | Sat | MM/dd/yyyy |
| 10/06/2024 | Sun | MM/dd/yyyy |
| empty | empty | empty |
| empty | empty | empty |
| 07/10/2024 | Mon | dd/MM/yyyy |
| 08/10/2024 | Tue | dd/MM/yyyy |
| 09/10/2024 | Wed | dd/MM/yyyy |
| 10/10/2024 | Thu | dd/MM/yyyy |
I don’t know why, some rows are formatted as MM/dd/yyyy and anything I did helped me.
I can assure you that every cell in column A is formatted as italian date dd/MM/yyyy.
I’ve seen that I can make it work setting all dates variables as Strings, but I need them as dates for other formulas I use.
Any other suggestion about how make the same thing in a more efficient way would be greatly appreciated!
Remove all the formatting stuff – format is for display only:
Sub btnOK_Click()
Dim myDate As Date
Dim start_date As Date
Dim end_date As Date
Dim k As Long, cont As Long
start_date = DateValue(textStart.Value)
end_date = DateValue(textEnd.Value)
myDate = start_date
k = 0
cont = 1 ' Every 7 days i want 2 rows-space
Range("A1").Select
Do Until myDate = end_date + 1
Selection.Offset(k, 0).Select
' Apply desired format to the cell, not the value.
Selection.Value = myDate
myDate = myDate + 1
k = 0
If cont = 7 Or cont Mod 7 = 0 Then
k = k + 3
Else
k = k + 1
End If
cont = cont + 1
Loop
End Sub