Need help to increment date column with macro (repost)

dwest100

Board Regular
Joined
Jan 2, 2005
Messages
138
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!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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

Board Regular
Joined
Jan 2, 2005
Messages
138
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

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
Record two macros of the above actions for increment/decrement. Add two buttons to the sheet and attach the macros.
 

dwest100

Board Regular
Joined
Jan 2, 2005
Messages
138

ADVERTISEMENT

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

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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:
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

Board Regular
Joined
Jan 2, 2005
Messages
138

ADVERTISEMENT

Wow!
Thanks psitaram!

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

dwest100

Board Regular
Joined
Jan 2, 2005
Messages
138
Hi psitaram,
This works great! (I knew it would after all :biggrin: )

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

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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

Board Regular
Joined
Jan 2, 2005
Messages
138
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
 

Forum statistics

Threads
1,147,508
Messages
5,741,568
Members
423,667
Latest member
Kai_357

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
Top