add incremental dates in column by user defined period

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
If I follow what you're after,
maybe something along the lines of this will work
VBA Code:
    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)
 
Upvote 0
If I follow what you're after,
maybe something along the lines of this will work
VBA Code:
    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
 
Upvote 0
try
VBA Code:
    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)
 
Upvote 0
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
VBA Code:
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.
 
Upvote 0
maybe this
VBA Code:
    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
 
Upvote 0
Solution
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.

VBA Code:
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:

IrowDateSr No.
128-02-20221
228-02-20222
328-02-20223
428-02-20224
528-02-20225
628-02-20226
728-02-20227
803-03-20221
918-03-20221
1031-03-20221
1101-04-20221
1228-02-20228
1322-03-20221
1422-03-20222
1506-03-20221
1606-06-20221
1706-03-20222
1807-03-20221
1907-03-20222
2001-04-20222
 
Upvote 0
maybe this
VBA Code:
    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.
VBA Code:
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:
IrowDateSr No.
128-02-20221
228-02-20222
328-02-20223
428-02-20224
528-02-20225
628-02-20226
728-02-20227
803-03-20221
918-03-20221
1031-03-20221
1101-04-20221
1228-02-20228
1322-03-20221
1422-03-20222
1506-03-20221
1606-06-20221
1706-03-20222
1807-03-20221
1907-03-20222
2001-04-20222
 
Upvote 0
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
 
Upvote 0
Yes I do understand.
Will post a separate question
Many thanks.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top