Adding dates to a column based on cell value

[Maleko-1]

Hello All,

I am working on sort of a schedule. In column A is dates, column B is names and column C is a number. What I am looking for is the date in column A to be added and increase the number of times the column C value is while excluding weekends. I also need the names in column B to be copied the same amount of times in column B.
For example:

A B C
8/25/11 John 4
8/26/11 John
8/29/11 John
8/30/11 John
9/16/11 Dave 3
9/19/11 Dave
9/20/11 Dave

These values are being entered on the worksheet from a Userform. I can get the first row but I would like an easier way to add the rest instead of entering each day manually.
Any help with this would be much appreciated.

Marc

 

[jim may]

Show us the final Layout of the above, AFTERWARDS.

 

[Peter_SSs]

Show us the final Layout of the above, AFTERWARDS.

My guess is that is what we have been shown.

Marc

You haven't told us much about your UserForm so I have just assumed 3 Text Boxes to collect the first date (as a string) (TextBox1), the name (TextBox2) and the number of rows (TextBox3). If that is the case the the code might go roughly like this. (Command Button on the UserForm?)

The code may need to do some additional formatting for the dates perhaps.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    nr = Range("A" & Rows.Count).End(xlUp).Row + 1<br>    <SPAN style="color:#00007F">With</SPAN> Range("A" & nr).Resize(TextBox3.Value)<br>        .FormulaR1C1 = "=WORKDAY(R[-1]C,1)"<br>        .Cells(1, 1).Value = DateValue(TextBox1.Value)<br>        .Offset(, 1).Value = TextBox2.Value<br>        .Resize(1).Offset(, 2).Value = TextBox3.Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    UserForm1.Hide<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Maleko-1]

Thanks for the reply.

The code you provided works great for the 1st entry. When I open the Userform and send another entry it places the name correctly but the date is increasing according to the last date of the previous entry.

The results I am geting are:
A B C
8/25/11 John 4
8/26/11 John
8/29/11 John
8/30/11 John
8/31/11 Dave 3
9/01/11 Dave
9/02/11 Dave

The results I need are:
A B C
8/25/11 John 4
8/26/11 John
8/29/11 John
8/30/11 John
9/16/11 Dave 3
9/19/11 Dave
9/20/11 Dave

Again, thanks for taking the time to help me witht his issue.

Marc

 

[Maleko-1]

I have tried some different modifications to the code but I am not having any luck.
I was also thinking that I will need to have the capability of possible duplicate entries since this is somewhat of a vacation schedule.
It seems as if I am missing a simple little piece of code.

 

[Peter_SSs]

When I open the Userform and send another entry it places the name correctly but the date is increasing according to the last date of the previous entry.

That is not happening for me. Here is the result of me running the UserForm twice.

Excel Workbook
	A	B	C
1
2	15/07/11	Bill	3
3	18/07/11	Bill
4	19/07/11	Bill
5	27/07/11	Jen	5
6	28/07/11	Jen
7	29/07/11	Jen
8	1/08/11	Jen
9	2/08/11	Jen
10
Enter data from userform

The relevant part of my code is this

.FormulaR1C1 = "=WORKDAY(R[-1]C,1)"
.Cells(1, 1).Value = DateValue(TextBox1.Value)

The red part puts formula in that increases column A to the next workday from the cell above. So after the red line executes, for Bill the dates are
2 Jan 1900
3 Jan 1900
4 Jan 1900

However, the blue part then puts in the correct start date for Bill (15 Jul 2011 from TextBox1) in cell A2 and that causes A3 to change by its formula to 18 Jul 2011 and A4 to change to 19 Jul 2011.

When we process the Jen section, again the red section inserts formula in A5:A9 producing dates following on from the last Bill date of 19 Jul 2011. However the blue line then replaces the first 'Jen date' (27 Jul 2011) in cell A5 from TextBox1 and again this causes the other 'Jen' date cells to adjust to the correct dates.

Perhaps you need to tell us more about your UserForm, its components and the code you are currently running.

 

[Maleko-1]

Peter_SSs,

Please accept my apology. Your code worked perfectly. I was toying around with so much in the code and the worksheet that I created a mess.
Thanks for your time helping me with this. This message board is the best!

Marc

 

[Peter_SSs]

No problem. Glad you seem to have a successful outcome in the end.