Create a days of month using a date as parameter

Question

I have a date in cell A1 for example: 12/08/22

I want create a list with all days of the month (1-31) in column E using the month and year of the cell A1 as parameter.

Sub TESTEEEEEEE()

    Dim r As Range, i As String, j As Long
     i = Range("A1").Offset(0, 1).Value = Left(cell.Value, 3)
    'k = ????
    j = 31
    For Each r In Range("E1:E31")
        r.NumberFormat = "@"
        r.Value = Format(DateSerial(k, i, j), "dd/m/yy")
         j = j + 1
    Next r
End Sub

I'm stucked in how to extract the the month and year. I was trying using the position of the characteres as parameter, but i'm not getting it work.

i should extract the 4,5 and characterer returning 08 (ok, the code is wrong i was making some tests).

k should extract the 7,8 charachter returning 22.

Someone can help me?

Answer 1

Please, try using the next way. It does not need iteration:

Sub testCreateWholeMonth()
   Dim D As Date, lastD As Long
   D = Range("A1").value
   
   lastD = Day(WorksheetFunction.EoMonth(DateSerial(Year(D), Month(D), 1), 0))
   With Range("E1:E" & lastD)
        .value = Evaluate("date(" & Year(D) & "," & Month(D) & ",row(1:" & lastD & "))")
        .NumberFormat = "mm.dd.yyyy"
    End With
End Sub