CODEWITHSUNDEEP
excelvbaexcel-formulams-office
Black Mamba
Black Mamba

Reputation: 245

Create a days of month using a date as parameter

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?

Upvotes: 0

Views: 154

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 2

Storax
Storax

Reputation: 12167

You are probably after calendar functions like YEAR, MONTH, EOMONTH

Sub DebugDate()

    Dim rg As Range
    Set rg = Range("A1")  ' should contain a date
    Dim dt As Date
    dt = rg.Value

    Debug.Print Year(dt), Month(dt), CDate(WorksheetFunction.EoMonth(dt, 0))
    ' End of month not using worksheet function EOMONTH
    Debug.Print DateSerial(Year(dt), Month(dt) + 1, 1) - 1

End Sub

Further reading on How to create a calendar with VBA

Upvotes: 2

Related Questions