0

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.

NCoding
  • 29
  • 4
  • `Debug.Print Format$(J, "mm/yyyy")` – braX Nov 19 '21 at 08:31
  • Thanks braX but i want only 1 output of "08/2021" not one for every days. Here is the actual output: ``` 07/2021 07/2021 08/2021 08/2021 08/2021 08/2021 08/2021 ``` – NCoding Nov 19 '21 at 08:33
  • Do a for next loop from the month and year of the min date to the month and year of the max date – Nathan_Sav Nov 19 '21 at 08:40

1 Answers1

0

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.

braX
  • 11,506
  • 5
  • 20
  • 33
  • Also, you may want to read about [Better way to find last used row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) – braX Nov 19 '21 at 11:49