Auto-populate other cells based on dates

[Freddy]

Microsoft Visual Basic 6.5 on Microsoft Excel 2002 SP3. I'd like assistance
in writing code to auto-populate other cells based on a column of dates. The
purpose is to determine what slots need to be filled for scheduling purposes.
There are a total of, for example, 15 slots to be filled per date.
Essentially, this is my thought process. I believe each date per row needs to
be evaluated to see where it fits in a scheduling template which would be
created by the user initially selecting a date using a defined name table
then I would have the cells underneath fill in automatically 15 times then
the 16th cell down would be the originally selected date plus one day; this
last step would be repeated to span 7 days. Now, from another worksheet
within the workbook, after sorting records by date, then time, then city, I
would these copy rows into a worksheet named "Scheduler".

 

[Luke M]

An bit of code that creates the starting point of your scenario:

'===============
Sub AutoDates()
Dim StartDate, EndDate As Date
Dim i, x As Integer

'Define the start date
StartDate = Range("A1")

'Number of Days
EndDate = StartDate + 7
'Starting row
x = 2

Do While StartDate <= EndDate
'Number of entries per day
i = 15
Do While i > 0
Cells(x, 1).Value = StartDate
i = i - 1
x = x + 1
Loop
StartDate = StartDate + 1
Loop
End Sub
'============

 

[avelez]

Superb code Luke M. The following elaborates on my ultimate goal.
Please read on and let me know if you can help.

Based on Columns A, B, C, and D, as shown below, the user selects the
date using a data validation scheme in the cell where you see the date
"Thu, 10/22/09" under the column header "Select Start Date". The 6
cells below said data are filled in automatically using the formula
"=A2+1", "=A3+1", etc. The other 3 columns labeled "Date", "Time", and
"Service City", are a result of a copy of said data from another
worksheet named "SourceData".

(Columns A, B, C, and D, located in a worksheet named "Scheduler".)
Select Start Date Date Time Service City
Thu, 10/22/09 Mon, 10/26/09 PM CRANFORD
Fri, 10/23/09 Tue, 10/27/09 AM CRANFORD
Sat, 10/24/09 Tue, 10/27/09 AM CRANFORD
Sun, 10/25/09 Tue, 10/27/09 AM CRANFORD
Mon, 10/26/09 Tue, 10/27/09 AM CRANFORD
Tue, 10/27/09 Tue, 10/27/09 AM CRANFORD
Wed, 10/28/09 Tue, 10/27/09 AM CRANFORD

The goal now is to evaluate and compare the dates in Column B with the
scheduling range of dates in Column A then copy/auto-populate the
values in Columns B, C, and D, to a template as shown below:Any empty
slots would be what the user would use as a basis to make phone calls
and present the customer with said slots to fill. Note, I've shortened
the number of rows in this reply for obvious reasons.

(Columns H, I, J, and K of the same worksheet named "Scheduler".)
Date Time Service City
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
..
..
..

I would include the worksheet as an attachment if someone would be
kind enough to instruct me. Thank you.

An bit of code that creates the starting point of your scenario:

'===============
Sub AutoDates()
Dim StartDate, EndDate As Date
Dim i, x As Integer

'Define the start date
StartDate = Range("A1")

'Number of Days
EndDate = StartDate + 7
'Starting row
x = 2

Do While StartDate <= EndDate
'Number of entries per day
i = 15
Do While i > 0
Cells(x, 1).Value = StartDate
i = i - 1
x = x + 1
Loop
StartDate = StartDate + 1
Loop
End Sub
'============
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*





- Show quoted text -

Based on Columns A, B, C, and D, as shown below, the user selects the
date using a data validation scheme in the cell where you see the date
"Thu, 10/22/09" under the column header "Select Start Date". The 6
cells below said data are filled in automatically using the formula
"=A2+1", "=A3+1", etc. The other 3 columns labeled "Date", "Time", and
"Service City", are a result of a copy of said data from another
worksheet named "SourceData".

(Columns A, B, C, and D, located in a worksheet named "Scheduler".)
Select Start Date Date Time Service City
Thu, 10/22/09 Mon, 10/26/09 PM CRANFORD
Fri, 10/23/09 Tue, 10/27/09 AM CRANFORD
Sat, 10/24/09 Tue, 10/27/09 AM CRANFORD
Sun, 10/25/09 Tue, 10/27/09 AM CRANFORD
Mon, 10/26/09 Tue, 10/27/09 AM CRANFORD
Tue, 10/27/09 Tue, 10/27/09 AM CRANFORD
Wed, 10/28/09 Tue, 10/27/09 AM CRANFORD

The goal now is to evaluate and compare the dates in Column B with the
scheduling range of dates in Column A then copy/auto-populate the
values in Columns B, C, and D, to a template as shown below:Any empty
slots would be what the user would use as a basis to make phone calls
and present the customer with said slots to fill. Note, I've shortened
the number of rows in this reply for obvious reasons.

(Columns H, I, J, and K of the same worksheet named "Scheduler".)
Date Time Service City
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Thu, 10/22/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
Fri, 10/23/09
..
..
..

I would include the worksheet as an attachment if someone would be
kind enough to instruct me. Thank you.