Populating dates

[treasuresflemar]

I have a range(thisRange) that I would like to populate the first column
with dates from January 1 to december 31 of an x year

Sub populateDate(yr as string)
Dim Myyear as date
dim thisrange as string
Dim xyear as string

Myyear = "Sales "& year(Worksheets("daily").range("b2").value
thisrange = "sales" & myyear
xyear - year(myyear)

How do I put in the dates for xyear?

 

[OssieMac]

Hi,

Because the code you have will not work, I am not really sure what you are
trying to do. Especially as it appears that you have a parameter (yr) in the
sub name that I assume you are passing the year to the procedure and then not
using.

However, the following code will find the year from a date in cell A1 and
the place the first day of the year in cell B2 and populate the cells below
with the dates for the entire year.

I suggest that you copy the macro into a module in a blank workbook and
insert any valid date in cell A1 of the active sheet and run the macro to see
what it does. You then might be able to work out what you need from the
samples of code.

Sub test()

Dim MyYear As Long
Dim dateFirst As Date
Dim dateLast As Date
Dim rngFirst As Range
Dim numbDays As Long


'The following line returns a number representing
'the year from a date in range A1
MyYear = Year(Worksheets("daily").Range("A1").Value)

'Date of first day of year from MyYear
dateFirst = DateValue(DateValue("January 1," & MyYear))

'Date of last day of year from MyYear
dateLast = DateValue(DateValue("December 31," & MyYear))


'Assign start cell to a variable
'Edit to your start cell
Set rngFirst = Range("B2")

'Number of days in the year (Required to allow for leap year)
'plus the row number of the first date.
numbDays = (dateLast - dateFirst) + rngFirst.Row

'Populate first cell with dateFirst
rngFirst.Value = dateFirst

'Autofill for number of days in year
rngFirst.AutoFill Destination:=Range(rngFirst, _
Cells(numbDays, "B")), Type:=xlFillDefault


End Sub

End Sub

 

[OssieMac]

Hi again,

Replace the DateValue lines with this.

'Date of first day of year from MyYear
dateFirst = DateValue("January 1," & MyYear)

'Date of last day of year from MyYear
dateLast = DateValue("December 31," & MyYear)

I put DateValue function in twice in each line. I actually wrote the first
line and then copied it and edited it for the second one and hence the error
twice. I recall it telling me that I had an error and I put in an extra
bracket on the end to fix it.