Need help to increment date column with macro (repost)

[dwest100]

I have a column of dates (column d). New dates are added and deleted regularly.

I need to be able to increment all the dates in the column by a specific number of days, forward or backward, using a macro.

The macro needs to pop open a dialog window to prompt me for the number of days I wish to increment the dates in the column, and also whether to index forward or backward depending on which radio button is selected (forward or backward). Default would be forward.

How can I do this?

Any help would be greatly appreciated!

 

[P Sitaram]

Enter the number by which you want to increment/decrement in a cell.
Copy the cell.
Select the date range, choose Paste Special and Add or Subtract.
Format the date range as dates.

edit: you can choose Values and Add/Subtract. The date range format will be preserved.

 

[dwest100]

Thanks psitaram but I actually need a macro like I described. This is going to be used by a bunch of total novices and they need to click a button to increment the dates.

Is there a way to create a macro like I described??

 

[P Sitaram]

Record two macros of the above actions for increment/decrement. Add two buttons to the sheet and attach the macros.

 

[dwest100]

Thanks again.
Sorry, I should have been more detailed.

I see your approach but the users I'm dealing with want specifically to click an "adjust dates" menu item which pops up a form with an entry box for the number of days and a pair of radio buttons for forward and backward. They want to enter the number of days, click increment or decrement and click OK. then all the dates in the column adjust accordingly.

I know how to set up the menu item but I'm not sure how to create the macro.

Any ideas along these specific lines would be greatly appreciated.

 

[P Sitaram]

Set up the userform with a text box, two option buttons in a frame and two command buttons for OK and cancel.

Assume that the dates are in A1:A10

In the userform, for the two command buttons, paste this code:

Private Sub CommandButton1_Click()
    Dim DateCell As Range
    If Not IsNumeric(Me.TextBox1.Value) Then Exit Sub
    Me.TextBox1.Value = Int(Me.TextBox1.Value)

    If Me.OptionButton1.Value = True Then
        For Each DateCell In Sheets(1).[a1:a10]
            DateCell = DateCell + Me.TextBox1.Value
        Next DateCell
    ElseIf Me.OptionButton2.Value = True Then
        For Each DateCell In Sheets(1).[a1:a10]
            DateCell = DateCell - Me.TextBox1.Value
        Next DateCell
    Else
        MsgBox "Select increase or decrease!"
        Exit Sub
    End If
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

Set you menu items action to show the form.

 

[dwest100]

Wow!
Thanks psitaram!

I'll give that a try and let you know how it goes.

 

[dwest100]

Hi psitaram,
This works great! (I knew it would after all )

I have one question. How can I adjust the macro to automatically select the range from A4 to the last A cell with a value in it??

I greatly appreciate your help! I am learning and this is a great teaching aide as well as solving the problem.

 

[P Sitaram]

Change this line(in both places):

For Each DateCell In Sheets(1).[a1:a10]

to

For Each DateCell In Sheets(1).rRnge("a4:a" & Sheets(1).[a65536].End(xlUp).Row)

 

[dwest100]

Thank you psitaram!
It's working great.

There is one minor problem I have been trying to solve though.
Upon first displaying the form, I must click cancel twice in order to close the form.

Likewise, upon displaying the form, I must run the macro twice before the form will close automatically.

Here is the code:

Private Sub CommandButton1_Click()
Dim DateCell As Range
If frmAdjustSchedule.OptionButton1.Value = False And frmAdjustSchedule.OptionButton2.Value = False Then
MsgBox "Please select forward or backward."
Exit Sub
ElseIf Not IsNumeric(frmAdjustSchedule.TextBox1.Value) Then
MsgBox "Please enter the number of days to adjust the schedule."
Exit Sub
End If

myIncr = Int(frmAdjustSchedule.TextBox1.Value)

If frmAdjustSchedule.OptionButton1.Value = True Then
For Each DateCell In Sheets(1).Range("d4:d" & Sheets(1).[d65536].End(xlUp).Row)

DateCell = DateCell + myIncr
Next DateCell

ElseIf frmAdjustSchedule.OptionButton2.Value = True Then
For Each DateCell In Sheets(1).Range("d4:d" & Sheets(1).[d65536].End(xlUp).Row)

DateCell = DateCell - myIncr
Next DateCell
End If
frmAdjustSchedule.Hide
End Sub

Private Sub CommandButton2_Click()
frmAdjustSchedule.Hide
End Sub

Can you advise on what would cause the problem.

Thanks again! You've been a huge help.
Don