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

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

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.

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 -

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 -