Need help with macro to increment dates in column 1

[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!

 

[johnwm]

To increment a cells value:

Cells(myRow, myCol).Value = Cells(myRow, myCol).Value + 1



________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

For tsunami relief donations

'If we're supposed to work in Hex, why have we only got A fingers?'

http://www.essexsteam.co.uk

for steam enthusiasts

 

[xlbo]

I would use a combobox / Textbox to store the number and then convert it to +/- dependant on check on option button
Set a frame up around the option buttons to take a 1 or a -1 value dependant on the option chosen

myIncr = ComboboxName.value * FrameName.value

LastRow = cells(65536,4).end(xlup).row
with Range("D" & LastRow +1)
    .value = myIncr
    .copy
end with
with Range("D2:D" & LastRow)
   .PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
end with
range("D" & LastRow).clearcontents

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

Thanks guys!
xlbo, I understand what you are saying but please elaborate on how to set the frame to assume the value of the checked option box.
Thanks!

 

[xlbo]

Apologies - thinking in MSAccess mode

In excel, you would have to check the value of one of the option buttons

If OptIncr = TRUE then
 myIncr = ComboboxName.value
Else
 'OptDecr must be checked
 myIncr = ComboboxName.value * -1
End If

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

Here is what I have come up with. It works...sort of. I have to click cancel twice to unload the form. Also, I have to run the macro twice for the form to unload after processing the dates.

Any ideas to fix it?

Private Sub CommandButton1_Click()
    Dim DateCell As Range
    If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then
        MsgBox "Please select forward or backward."
        Exit Sub
    ElseIf Not IsNumeric(Me.TextBox1.Value) Then
        MsgBox "Please enter the number of days to adjust the schedule."
        Exit Sub
    End If
    
    Me.TextBox1.Value = Int(Me.TextBox1.Value)
    
    If Me.OptionButton1.Value = True Then
        For Each DateCell In Sheets(1).[d4:d400]
            DateCell = DateCell + Me.TextBox1.Value
        Next DateCell
        
    ElseIf Me.OptionButton2.Value = True Then
        For Each DateCell In Sheets(1).[d4:d400]
            DateCell = DateCell - Me.TextBox1.Value
        Next DateCell
    End If
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    Unload Me
End Sub

 

[xlbo]

I would take Textbox1.value into a variable and HIDE the form rather than unloading

Private Sub CommandButton1_Click()
    Dim DateCell As Range
    If Me.OptionButton1.Value = False And Me.OptionButton2.Value = False Then
        MsgBox "Please select forward or backward."
        Exit Sub
    ElseIf Not IsNumeric(Me.TextBox1.Value) Then
        MsgBox "Please enter the number of days to adjust the schedule."
        Exit Sub
    End If
    
    myIncr = Int(Me.TextBox1.Value)
    
    If Me.OptionButton1.Value = True Then
        For Each DateCell In Sheets(1).[d4:d400]
            DateCell = DateCell + myIncr
        Next DateCell
        
    ElseIf Me.OptionButton2.Value = True Then
        For Each DateCell In Sheets(1).[d4:d400]
            DateCell = DateCell - myIncr
        Next DateCell
    End If
    Me.Hide
End Sub

However, I would advise against looping unless necessary - the pastespecial - Add function will do nicely here

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

xlbo,
Many thanks! Pardon my ignorance as I'm new at this. How would you incorporate the pastespecial add code into my code?
Don

 

[xlbo]

As I did in my 1st post ;-)

If optionbutton1 = true then
  myIncr = Int(Me.TextBox1.Value)
else
  myIncr = Int(Me.TextBox1.Value) *-1

LastRow = cells(65536,4).end(xlup).row

with Range("D" & LastRow +1)
    .value = myIncr
    .copy
end with
with Range("D2:D" & LastRow)
   .PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
end with
range("D" & LastRow).clearcontents

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

I used your suggested code above but I still have to run the macro twice before the form will hide. (or unload)

What would be causing that?

Same with the cancel button. If I call the macro, the form opens as expected, but I have to click cancel twice to close the form.

 

[xlbo]

Is the button ON the form ??

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

Yes. Both buttons are.
This is the first form I've built. But in design mode, the buttons are clearly on the form.
Is that all that's required or am I missing the boat on something form related??

Thanks!

 

[xlbo]

If the button is on the form and you have Me.Hide then it should work. The only issue might be in using the ME keyword - it may be applying itself to the button being pressed rather than the form - try explicity stating the form name rather than using ME - see if it makes a difference

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[dwest100]

OK will do.

Thanks for all your help. Much appreciated!!