How do I write a VBA for 3 consecutive dates?

[Guest]

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

 

[WhytheQ]

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

 

[Guest]

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


I tried it and I got an "Invalid procedure or argument" notice that highlighted the Cells(z, 2) = DateAdd("dd", y, Startdate) line.

 

[WhytheQ]

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

 

[Guest]

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]

Glad I could help a bit.
p.s your initial code was pretty accomplished and yet you called
yourself a newbie?!