Hi abac,
Assuming the month in cell A1 is text and not a date formatted as a month and you want dates in Col. B and days in Col. C (or else the days will overwrite the dates) this should do the job:
Option Explicit
Sub Macro1()
Dim intMonth As Integer
Dim intMonthDays As Integer
Dim i As Long
Dim lngLastRow As Long
Dim lngMyRow As Long
Application.ScreenUpdating = False
intMonth = Evaluate("VLOOKUP(A1,{""January"",1;""February"",2;""March"",3;""April"",4;""May"",5;""June"",6;""July"",7;""August"",8;""September"",9;""October"",10;""November"",11;""December"",12},2,0)")
intMonthDays = Day(DateSerial(Year(Now()), intMonth + 1, 1) - 1) 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=400
lngLastRow = Range("B:C").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If lngLastRow >= 3 Then
Range("B3:C" & lngLastRow).ClearContents
End If
For i = 1 To intMonthDays
If IsDate(i & "/" & intMonth & "/" & Year(Now())) = True Then
If i = 1 Then
lngMyRow = 3
Else
lngMyRow = lngMyRow + 1
End If
Range("B" & lngMyRow).Value = CDate(i & "/" & intMonth & "/" & Year(Now())) 'Date
Range("C" & lngMyRow).Value = i 'Day
End If
Next i
Application.ScreenUpdating = True
End Sub
Note too that the year used is from today's date.
HTH
Robert
Bookmarks