Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a VBA for 3 consecutive dates?
I am a VBA newbie and I am reformatting some older data by entering two
consecutive dates on separate rows. For example A1 (1/1/2006) has a date and I need A2 and A3 to equal the next two days (i.e., 1/2/2006 and 1/3/2006). I also dont know how to define cells as dates and how to assign a value defined by a variable to a cell. The code below makes logical sense but the syntax is wrong and I dont know how to fix it. Thank you for your help. Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2).Date 'defining the Startdate Cells(z, 2).Date = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a VBA for 3 consecutive dates?
Sub Dates_OfCapture()
Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer Dim y As Integer 'ADDED Dim z As Integer 'ADDED x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2) '.Date 'defining the Startdate 'ALTERED Cells(z, 2) = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub ....is that any better? J On Oct 18, 5:36 pm, Mig wrote: I am a VBA newbie and I am reformatting some older data by entering two consecutive dates on separate rows. For example A1 (1/1/2006) has a date and I need A2 and A3 to equal the next two days (i.e., 1/2/2006 and 1/3/2006). I also don't know how to define cells as dates and how to assign a value defined by a variable to a cell. The code below makes logical sense but the syntax is wrong and I don't know how to fix it. Thank you for your help. Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2).Date 'defining the Startdate Cells(z, 2).Date = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a VBA for 3 consecutive dates?
"WhytheQ" wrote: Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer Dim y As Integer 'ADDED Dim z As Integer 'ADDED x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2) '.Date 'defining the Startdate 'ALTERED Cells(z, 2) = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub ....is that any better? J On Oct 18, 5:36 pm, Mig wrote: I am a VBA newbie and I am reformatting some older data by entering two consecutive dates on separate rows. For example A1 (1/1/2006) has a date and I need A2 and A3 to equal the next two days (i.e., 1/2/2006 and 1/3/2006). I also don't know how to define cells as dates and how to assign a value defined by a variable to a cell. The code below makes logical sense but the syntax is wrong and I don't know how to fix it. Thank you for your help. Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2).Date 'defining the Startdate Cells(z, 2).Date = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub I tried it and I got an "Invalid procedure or argument" notice that highlighted the Cells(z, 2) = DateAdd("dd", y, Startdate) line. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a VBA for 3 consecutive dates?
Change the "dd" to just "d"
I just ran it with 01-Jan-06 in the cell B10 and it didn't increment by just one day at a atime but produced the following. Is this what you want?: 01-Jan-06 02-Jan-06 04-Jan-06 07-Jan-06 11-Jan-06 16-Jan-06 22-Jan-06 29-Jan-06 -- -- -- Rgds J On Oct 18, 6:23 pm, Mig wrote: "WhytheQ" wrote: Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer Dim y As Integer 'ADDED Dim z As Integer 'ADDED x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2) '.Date 'defining the Startdate 'ALTERED Cells(z, 2) = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub ....is that any better? J On Oct 18, 5:36 pm, Mig wrote: I am a VBA newbie and I am reformatting some older data by entering two consecutive dates on separate rows. For example A1 (1/1/2006) has a date and I need A2 and A3 to equal the next two days (i.e., 1/2/2006 and 1/3/2006). I also don't know how to define cells as dates and how to assign a value defined by a variable to a cell. The code below makes logical sense but the syntax is wrong and I don't know how to fix it. Thank you for your help. Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer x = 10 'starting row y = 1 'add one day For N1 = 1 To 100 For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 Startdate = Cells(x, 2).Date 'defining the Startdate Cells(z, 2).Date = DateAdd("dd", y, Startdate) 'adding a day to the row below the starting date x = x + 1 'add one to the the row number y = y + 1 'add one to the number of days beind added to Startdate Next N2 z = 1 'reset z (number of days added) back to 1 Next N1 End Sub I tried it and I got an "Invalid procedure or argument" notice that highlighted the Cells(z, 2) = DateAdd("dd", y, Startdate) line.- Hide quoted text -- Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
consecutive dates | Excel Discussion (Misc queries) | |||
consecutive bi-monthly dates | Excel Worksheet Functions | |||
how do I sort a column of random dates into Consecutive dates | Excel Worksheet Functions | |||
Printing consecutive dates | Excel Discussion (Misc queries) | |||
Assigning consecutive dates | Excel Worksheet Functions |