Date Loop in VBA

[msohare91]

Hi all,

Hoping someone can help with me with a small bit of code. I'm new to VBA and am struggling to make a simple loop work. I want to start with the first day of the current month (will be run fresh each month) and I have done this fine using dateserial function. Essentially I want to repeat each day in a given month 3 times and loop all the way to end of the month. So would read like:

01/06/2020
01/06/2020
01/06/2020
02/06/2020
02/06/2020
02/06/2020 and so on until the 30th.

I'm getting stuck between just repeating the code and looping.

Any help welcome.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub msohare()
   Dim ary As Variant
   Dim i As Long, j As Long
   Dim start As Long
   
   start = CLng(DateSerial(Year(Date), Month(Date), 1) - 1)
   i = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
   ReDim ary(1 To i * 3, 1 To 1)
   For j = 1 To i
      ary(j * 3 - 2, 1) = start + j
      ary(j * 3 - 1, 1) = start + j
      ary(j * 3, 1) = start + j
   Next j
   Range("a2").Resize(i * 3).Value = ary
   Range("A:A").NumberFormat = "dd/mm/yyyy"
End Sub

 

[Vathana]

Another one to try. Get each date from the current month, day 1 to end. This just print each date 3 times in the Immediate Window, so you can just change it as you wish, e.g. copy to a range in your sheet. I'm also new to VBA, so let's learn it together

Sub TestDate()

    Dim i As Integer, j As Integer, DaysOfMonth As Integer
  
    DaysOfMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
  
    For i = 1 To DaysOfMonth
  
        For j = 1 To 3
      
            'Print each date 3 times
            Debug.Print Format(DateSerial(Year(Date), Month(Date), i), "dd/mm/yy")
          
        Next j
  
    Next i
  
End Sub

 

[msohare91]

Hi & welcome to MrExcel.
How about

Sub msohare()
   Dim ary As Variant
   Dim i As Long, j As Long
   Dim start As Long
  
   start = CLng(DateSerial(Year(Date), Month(Date), 1) - 1)
   i = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
   ReDim ary(1 To i * 3, 1 To 1)
   For j = 1 To i
      ary(j * 3 - 2, 1) = start + j
      ary(j * 3 - 1, 1) = start + j
      ary(j * 3, 1) = start + j
   Next j
   Range("a2").Resize(i * 3).Value = ary
   Range("A:A").NumberFormat = "dd/mm/yyyy"
End Sub

Hi Fluff. Thanks for the welcome. This code worked great although some is over my head. Can I ask why you dim ary as Variant? Also how come the value in ary isn't placed until after the next statement? Usually I place all my values in variable at the start but just as point of practice.

Also cab you explain redim to me? Looks like re-use of storage but not really getting the explanation from office. And then why is resize requried? Interesting that you can multiply i. What is the 1 to 1 part following the i multiplication?

Sorry for all the questions thanks for the help really appreciate it!

 

[msohare91]

Another one to try. Get each date from the current month, day 1 to end. This just print each date 3 times in the Immediate Window, so you can just change it as you wish, e.g. copy to a range in your sheet. I'm also new to VBA, so let's learn it together

Sub TestDate()

    Dim i As Integer, j As Integer, DaysOfMonth As Integer
 
    DaysOfMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
 
    For i = 1 To DaysOfMonth
 
        For j = 1 To 3
     
            'Print each date 3 times
            Debug.Print Format(DateSerial(Year(Date), Month(Date), i), "dd/mm/yy")
         
        Next j
 
    Next i
 
End Sub

Hi Vathana

Thanks for your reply. Would be happy to learn together!! I couldn't get this bit of code to work but may be the order. I was trying to place my first date in A2 and loop from there. So I sued your DaysofMonth variable and tried to place it in A2. It gave me the first date but I think the debug.print may not have worked..

 

[Fluff]

The Redim resizes the array (ary) to the required size, in this case 90 rows by 1 column
The loop then puts values into the array & once the loop is finished, the array is written to the sheet in one go.

For more on arrays, have a look here

Excel VBA Array - The Complete Guide - Excel Macro Mastery

This post provides everything you need to know about the Excel VBA Array. Includes a quickVBA Array reference guide and 50+ VBA Array examples.

excelmacromastery.com

 

[Vathana]

W

Hi Vathana

Thanks for your reply. Would be happy to learn together!! I couldn't get this bit of code to work but may be the order. I was trying to place my first date in A2 and loop from there. So I sued your DaysofMonth variable and tried to place it in A2. It gave me the first date but I think the debug.print may not have worked..

With my codes, you'd have to implement your own to write the dates to your sheet, so Fluff's is solid.
And here is my amended codes using Fluff's method for your information. This writes the dates in Sheet4 (Code name) starting from range "A1".

Sub TestDate()

    Dim i As Integer, DaysOfMonth As Integer, ary As Variant
   
    DaysOfMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
   
    ReDim ary(1 To DaysOfMonth * 3, 1 To 1)
   
    For i = 1 To DaysOfMonth
   
        ary(i * 3 - 2, 1) = DateSerial(Year(Date), Month(Date), i)
        ary(i * 3 - 1, 1) = DateSerial(Year(Date), Month(Date), i)
        ary(i * 3, 1) = DateSerial(Year(Date), Month(Date), i)
   
    Next i
   
    Sheet4.Range("A1").Resize(DaysOfMonth * 3).Value = ary
   
End Sub

 

[msohare91]

W

With my codes, you'd have to implement your own to write the dates to your sheet, so Fluff's is solid.
And here is my amended codes using Fluff's method for your information. This writes the dates in Sheet4 (Code name) starting from range "A1".

Sub TestDate()

    Dim i As Integer, DaysOfMonth As Integer, ary As Variant
  
    DaysOfMonth = Day(DateSerial(Year(Date), Month(Date) + 1, 1) - 1)
  
    ReDim ary(1 To DaysOfMonth * 3, 1 To 1)
  
    For i = 1 To DaysOfMonth
  
        ary(i * 3 - 2, 1) = DateSerial(Year(Date), Month(Date), i)
        ary(i * 3 - 1, 1) = DateSerial(Year(Date), Month(Date), i)
        ary(i * 3, 1) = DateSerial(Year(Date), Month(Date), i)
  
    Next i
  
    Sheet4.Range("A1").Resize(DaysOfMonth * 3).Value = ary
  
End Sub

This works well thanks. Can I ask why the +1 is required in dateserial within the daysofmonth variable? It fills in all dates without this so am curious to know it's use?

 

[Fluff]

If you remove the +1 then you will get dates for 1st July.
By adding 1 to the month, you get the first of nest month & then there is the -1 at the end, which converts that to the last day of this month.

 

[msohare91]

If you remove the +1 then you will get dates for 1st July.
By adding 1 to the month, you get the first of nest month & then there is the -1 at the end, which converts that to the last day of this month.
[/Ah I see. I was trying to find more on this date formatting but couldn't quite see the logic. That makes sense thanks very much. So the plus 1,2,3 etc is simply adding x amount of days on to the end of current month and the negative 1,2,3 etc is used to take these off to keep it as one whole month. Great.