Auto generate duplicate Dates

[pwill]

Hi can anyone help with a formula or macro?

I have Headers in cells "A1"(Date) "B1"(Seq) and "L1"(Next) Sheet1

"B2:B" has a sequence of numbers
"L2" auto generates dates
"L3" has either the letter A or B

I want to auto generate duplicate dates in ("A2:A") down to the last row in column B based on data in cells "L2" and "L3"

ie if "L2" generates the date 01/02/18 and "L3" has the letter A then column A would generate dates as follows

	A	B	C	D	E	E	F	G	H	I	J	K	L	M
1	Date	Seq											Next
2	01/02/18	1											01/02/18
3	02/02/18	2											A
4	02/02/18	3
5	03/02/18	4
6	03/02/18	5
7	04/02/18	6
8	04/02/18	7
9	05/02/18	8
10	05/02/18	9
11	06/02/18	10
12	06/02/18	11
13	07/02/18	12
14	07/02/18	13
15

<colgroup><col style="width:48pt" width="64" span="15"> </colgroup><tbody>
</tbody>

ie if "L2" generates the date 01/02/18 and "L3" has the letter B then column A would generate dates as follows

	A	B	C	D	E	E	F	G	H	I	J	K	L	M
1	Date	Seq											Next
2	02/02/18	1											01/02/18
3	02/02/18	2											B
4	03/02/18	3
5	03/02/18	4
6	04/02/18	5
7	04/02/18	6
8	05/02/18	7
9	05/02/18	8
10	06/02/18	9
11	06/02/18	10
12	07/02/18	11
13	07/02/18	12
14
15

<colgroup><col style="width:48pt" width="64" span="15"> </colgroup><tbody>
</tbody>

Any help would be apprieciated

Regards

pwill

 

[pwill]

Just thought I would add that although they look very similar the difference between A and B is, if L3 has A the date in column A starts with the same date as L2 but only once as this would be added to data on sheet2 giving the duplicacate when added.

If L3 has B the dates start duplicating from the next day after the date in L2

hope that makes sense?

regards

 

[pwill]

Hi can anyone help with a formula or macro?

I have Headers in cells "A1"(Date) "B1"(Seq) and "L1"(Next) Sheet1

"B2:B" has a sequence of numbers
"L2" auto generates dates
"L3" has either the letter A or B

I want to auto generate duplicate dates in ("A2:A") down to the last row in column B based on data in cells "L2" and "L3"

ie if "L2" generates the date 01/02/18 and "L3" has the letter A then column A would generate dates as follows

	A	B	C	D	E	E	F	G	H	I	J	K	L	M
1	Date	Seq											Next
2	01/02/18	1											01/02/18
3	02/02/18	2											A
4	02/02/18	3
5	03/02/18	4
6	03/02/18	5
7	04/02/18	6
8	04/02/18	7
9	05/02/18	8
10	05/02/18	9
11	06/02/18	10
12	06/02/18	11
13	07/02/18	12
14	07/02/18	13
15

<tbody>
</tbody>

ie if "L2" generates the date 01/02/18 and "L3" has the letter B then column A would generate dates as follows

	A	B	C	D	E	E	F	G	H	I	J	K	L	M
1	Date	Seq											Next
2	02/02/18	1											01/02/18
3	02/02/18	2											B
4	03/02/18	3
5	03/02/18	4
6	04/02/18	5
7	04/02/18	6
8	05/02/18	7
9	05/02/18	8
10	06/02/18	9
11	06/02/18	10
12	07/02/18	11
13	07/02/18	12
14
15

<tbody>
</tbody>

Any help would be apprieciated

Regards

pwill

Help needed please, I have tried to write my own code if "L3" has the letter A but I don't know how to loop it through to the last row for me?

Sub autodates()

Dim dt As Date
Dim dtA As String
Dim dtB As String
Dim lrow As Long

lrow = Cells(Rows.Count, "B").End(xlUp).Row

dt = Range("L2").Value
dtA = Range("L3").Value
dtB = Range("L3").Value

r = 1
r = r + 1
If dtA = "A" Then

Cells(r, "A").Value = dt
Cells(r + 1, "A").Value = dt + 1
Cells(r + 2, "A").Value = dt + 1
Cells(r + 3, "A").Value = dt + 2
Cells(r + 4, "A").Value = dt + 2
Cells(r + 5, "A").Value = dt + 3
Cells(r + 6, "A").Value = dt + 3

End If

End Sub

can anyone help?

regards

pwill