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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I write a VBA for 3 consecutive dates?
Glad I could help a bit.
p.s your initial code was pretty accomplished and yet you called yourself a newbie?! On Oct 20, 2:38 am, Mig wrote: J, Thank you for your help. Using your suggestions I was able to make it work. My goal was to fill two consecutive dates following a start date. I was able to make it work so that it did the following INPUT OUTPUT 1/4/1990 1/4/1990 (start date) . 1/5/1990 . 1/6/1900 5/8/1990 5/8/1990 (start date) . 5/9/1990 . 5/10/1990 Bellow is the code I used. Thanks again for your help J. I hope this code will be useful to someone else. Sub Dates_OfCapture() Dim Startdate As Date Dim N1 As Integer Dim N2 As Integer Dim x As Integer Dim y As Integer Dim z As Integer x = 20 'starting row y = 1 'add one day Do While Cells(x, 2).Value < "" Startdate = Cells(x, 2) 'defining the Startdate For N2 = 1 To 2 'two turns of For Loop for every Startdate z = x + 1 'look at the cell bellow the Startdate Cells(z, 2) = DateAdd("d", 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 being added to Startdate Next N2 y = 1 'reset the number of days added to Startdate back to 1 x = x + 1 'add one more so that the next Startdate is not the last row but the following row Loop End Sub Cheers, Mig "WhytheQ" wrote: 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 -- 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 |