Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating dates
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating dates
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 -- Regards, OssieMac "treasuresflemar" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating dates
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. -- Regards, OssieMac "OssieMac" wrote: 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 -- Regards, OssieMac "treasuresflemar" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating Row with dates | Excel Worksheet Functions | |||
Populating dates into a 2003 Excel worksheet | Excel Discussion (Misc queries) | |||
formulas populating dates - 1st Tuesday | Excel Worksheet Functions | |||
Populating a column with calender dates............ | Excel Worksheet Functions | |||
populating a list box with weekly dates | Excel Worksheet Functions |