I have an issue with VBA, I am trying to loop into a date range with a monthly step but i'm struggling at the "monthly step" part.

Actually i am able to get this output:

13/08/2021 
14/08/2021 
15/08/2021 
16/08/2021 
17/08/2021 
18/08/2021

And what I am trying to get is more like:

08/2021 
09/2021 
10/2021 
11/2021 
12/2021 
01/2022 

Here is my code:

aIndex = 1
    For Each Cell In Range("F2", Range("F2").End(xlDown))
            aIndex = aIndex   1
            For J = Range("D" & aIndex) To Cell
                Debug.Print J
            Next J

    Next Cell

The "F" and "D" column simply contains dates with format "DD/MM/YYYY" and I'm looping between "D" and "F" date.

Thanks in advance,

Nicolas.

CodePudding user response：

There are lots of way to do it, but trying to keep it similar to what you have, removing things that are not needed, and using a Dictionary instead:

Sub test()
  Dim sDate As String
  Dim dict As Object
  Dim Cell As Range

  Set dict = CreateObject("Scripting.Dictionary")

  For Each Cell In Range("F2", Range("F2").End(xlDown))
     sDate = Format$(Range("D" & Cell.Row), "mm/yyyy")
     If Not dict.Exists(sDate) Then
       dict.Add sDate, 1
       Debug.Print sDate
     End If
  Next

  Debug.Print "Total: " & dict.Count

End Sub

There are things you can do with the Dictionary afterwards if your Debug.Print is not exactly what you needed.