add incremental dates in column by user defined period

[dss28]

I want to transfer date from textbox1 to cell B2 of sheet1. the user has two options by clicking option button 1 and option button 2
how should I do that
1st case: if the user chooses to increase the date for 7 days in next 7 rows
2nd case: if the user wants to increase the date by 7 days for next 7 rows

 

[NoSparks]

If I follow what you're after,
maybe something along the lines of this will work

    Dim i As Long, incrmnt As Long, ray(0 To 7)
    
    If IsDate(TextBox1) Then
        If OptionButton1 Then incrmnt = 1
        If OptionButton2 Then incrmnt = 7
    Else
        MsgBox "Sorry, that's not an accepted date."
        Exit Sub
    End If
    
    For i = 0 To 7
        ray(i) = CDate(TextBox1) + (i * incrmnt)
    Next i
    
    Sheets("Sheet1").Range("B2").Resize(8).Value = Application.Transpose(ray)

 

[dss28]

If I follow what you're after,
maybe something along the lines of this will work

    Dim i As Long, incrmnt As Long, ray(0 To 7)
   
    If IsDate(TextBox1) Then
        If OptionButton1 Then incrmnt = 1
        If OptionButton2 Then incrmnt = 7
    Else
        MsgBox "Sorry, that's not an accepted date."
        Exit Sub
    End If
   
    For i = 0 To 7
        ray(i) = CDate(TextBox1) + (i * incrmnt)
    Next i
   
    Sheets("Sheet1").Range("B2").Resize(8).Value = Application.Transpose(ray)

thanks NoSparks ....... yes, this is great.
Further
1) if instead of "7 " times (or rows), I want to control the number by textbox3 value which the user can add.
I tried replacing 7 by textbox3.value but shows error - " compile error : constant expression required"

2) If instead of cell B2 in sheet1 - I need to add the data below / after the last occupied cell in column B downwards

how can I still further modify the code. please guide. thanks in advance

 

[NoSparks]

try

    Dim i As Long, incrmnt As Long
    Dim Rws As Long, ray()
            
    If IsDate(TextBox1) Then
        Rws = TextBox3.Value
        If OptionButton1 Then incrmnt = 1
        If OptionButton2 Then incrmnt = 7
    Else
        Exit Sub
    End If
    
    ReDim ray(0 To Rws)
    For i = 0 To Rws
        ray(i) = CDate(TextBox1) + (i * incrmnt)
    Next i
    
    Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(Rws).Value = Application.Transpose(ray)

 

[dss28]

So much thank you..

only one thing happening is that :

1) For input - I use date format "dd-mmm-yyyy" in textbox1. While saving on sheet1 they are converted to "dd-mm-yyyy" and date format. With optionbutton1 dates are saved in "dd-mm-yyyy" and date format and is OK. However with optionbutton2, some of the values are saved in number format but display in "dd-mm-yyyyy").

I added a code in sheet1 as below but no effect on these values which are stored in number format

Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.Sheets("Sheet1").Columns("B:B").NumberFormat = "dd-mm-yyyy"
End Sub

2) Tried date format as "dd-mm-yyyy" in textbox1 but same thing is happening.

3) I need to maintain the date input in format "dd-mmm-yyyy" in the userform textbox1 as many other reports are based on this format.

4) Sorry for this addition as once we start using the form, we realise many things are missing from user point of view. Hence last little change I am adding is that of a textbox2 to add date addition value along with optionbutton1 and 2. So the user can have now three options to add date increment of 1 day by using optionbutton1, or 7 days by using optionbutton2 or any date increment as desired through textbox2 . While number of future dates / row input is done by textbox3.value.

 

[NoSparks]

maybe this

    Dim i As Long, incrmnt As Long, rws As Long
    Dim nr As Long      'next row
    Dim dte As Date     'starting date
    
    If IsDate(TextBox1) Then
        ' starting date
        dte = DateValue(TextBox1)
        ' rows to add
        rws = TextBox3.Value
        ' days to increment
        If Len(TextBox2) > 0 Then
            incrmnt = TextBox2
        Else
            If OptionButton1 Then incrmnt = 1
            If OptionButton2 Then incrmnt = 7
        End If
    Else
        Exit Sub
    End If
    ' write to sheet
    With Sheets("Sheet1")
        nr = .Range("B" & .Rows.Count).End(xlUp).Row + 1
        For i = 0 To rws
            .Cells(nr + i, "B") = DateAdd("d", i * incrmnt, dte)
            .Cells(nr + i, "B").NumberFormat = "dd-mm-yyyy"
        Next i
    End With

 

[dss28]

this has resolved my all issues. thanks

earlier when I was trasnferring data related for only one date, I was using following code to find out the maximum occurance of same date value in column B and then assign the max+1 value to any new date related data transferred to the next line.

Sub Test2()

Dim myVar As Long


On Error Resume Next

With ThisWorkbook.Sheets("CopyCalenderData")

ThisWorkbook.Sheets("CopyCalenderData").Range("M1").value = CDate(UserForm3.TextBox37.value)   ' sheet2

   myVar = .Evaluate("=MAX(IF(B:B=M1,C:C))")
   .Range("O1").value = myVar
   .Range("Q1").value = 1 + myVar
End With

End Sub

since now multiple date values are transferred in a click, how can I use this code to assign max+1 number to a particular date value in the column C in the same row.

I have included my desired table as follows:

Irow	Date	Sr No.
1	28-02-2022	1
2	28-02-2022	2
3	28-02-2022	3
4	28-02-2022	4
5	28-02-2022	5
6	28-02-2022	6
7	28-02-2022	7
8	03-03-2022	1
9	18-03-2022	1
10	31-03-2022	1
11	01-04-2022	1
12	28-02-2022	8
13	22-03-2022	1
14	22-03-2022	2
15	06-03-2022	1
16	06-06-2022	1
17	06-03-2022	2
18	07-03-2022	1
19	07-03-2022	2
20	01-04-2022	2

 

[dss28]

maybe this

    Dim i As Long, incrmnt As Long, rws As Long
    Dim nr As Long      'next row
    Dim dte As Date     'starting date
   
    If IsDate(TextBox1) Then
        ' starting date
        dte = DateValue(TextBox1)
        ' rows to add
        rws = TextBox3.Value
        ' days to increment
        If Len(TextBox2) > 0 Then
            incrmnt = TextBox2
        Else
            If OptionButton1 Then incrmnt = 1
            If OptionButton2 Then incrmnt = 7
        End If
    Else
        Exit Sub
    End If
    ' write to sheet
    With Sheets("Sheet1")
        nr = .Range("B" & .Rows.Count).End(xlUp).Row + 1
        For i = 0 To rws
            .Cells(nr + i, "B") = DateAdd("d", i * incrmnt, dte)
            .Cells(nr + i, "B").NumberFormat = "dd-mm-yyyy"
        Next i
    End With

this has resolved my all issues. thanks

earlier when I was trasnferring data related for only one date, I was using following code to find out the maximum occurance of same date value in column B and then assign the max+1 value to any new date related data transferred to the next line.

Sub Test2()

Dim myVar As Long


On Error Resume Next

With ThisWorkbook.Sheets("CopyCalenderData")

ThisWorkbook.Sheets("CopyCalenderData").Range("M1").value = CDate(UserForm3.TextBox37.value)   ' sheet2

myVar = .Evaluate("=MAX(IF(B:B=M1,C:C))")
.Range("O1").value = myVar
.Range("Q1").value = 1 + myVar
End With

End Sub

since now multiple date values are transferred in a click, how can I use this code to assign max+1 number to a particular date value in the column C in the same row.

I have included my desired table as follows:

Irow	Date	Sr No.
1	28-02-2022	1
2	28-02-2022	2
3	28-02-2022	3
4	28-02-2022	4
5	28-02-2022	5
6	28-02-2022	6
7	28-02-2022	7
8	03-03-2022	1
9	18-03-2022	1
10	31-03-2022	1
11	01-04-2022	1
12	28-02-2022	8
13	22-03-2022	1
14	22-03-2022	2
15	06-03-2022	1
16	06-06-2022	1
17	06-03-2022	2
18	07-03-2022	1
19	07-03-2022	2
20	01-04-2022	2

 

[NoSparks]

You're welcome.
Your original question has been answered, please mark post #6 as being the answer.

What you're asking now is different and as such should be posted as a new question,
I'm afraid I don't understand or follow what you're doing so you may want to include more details.

Thanks

 

[dss28]

Yes I do understand.
Will post a separate question
Many thanks.