plus 1

[alex]

Hello Experts,

I have an xls worksheet in which users paste a date from another
application.
Once pasted, I want to increase the value by one day (in the same
field).
Is that possible? And if so, how?


alex

 

[Guest]

ActiveCell.Value = ActiveCell.Value + 1

 

[alex]

ActiveCell.Value = ActiveCell.Value + 1

--
Regards,
Tom Ogilvy







- Show quoted text -

thanks Tom for your response.
I want to limit this function to only column B. How would I set that
up, and what Sub would I use?

 

[Rick Rothstein \(MVP - VB\)]

Try it this way....

If ActiveCell.Column = 2 Then ActiveCell.Value = ActiveCell.Value + 1

Rick

 

[alex]

Try it this way....

If ActiveCell.Column = 2 Then ActiveCell.Value = ActiveCell.Value + 1

Rick







- Show quoted text -

Thanks Rick for your help.
I also need to know what Sub you'd use, and if any arguments are
required...unless I'm misunderstanding where to place the code.

alex

 

[Rick Rothstein \(MVP - VB\)]

Thanks Rick for your help.

I also need to know what Sub you'd use, and if any arguments are
required...unless I'm misunderstanding where to place the code.

You can probably place it in the Worksheet Change event. Maybe like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurrentEnableEventsStatus As Boolean
CurrentEnableEventsStatus = Application.EnableEvents
Application.EnableEvents = False
If Target.Column = 2 Then Target.Value = Target.Value + 1
Application.EnableEvents = CurrentEnableEventsStatus
End Sub

Rick

 

[alex]

You can probably place it in the Worksheet Change event. Maybe like this...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurrentEnableEventsStatus As Boolean
CurrentEnableEventsStatus = Application.EnableEvents
Application.EnableEvents = False
If Target.Column = 2 Then Target.Value = Target.Value + 1
Application.EnableEvents = CurrentEnableEventsStatus
End Sub

Rick

That worked Rick. Thanks for your time.

alex

 

[alex]

That worked Rick. Thanks for your time.

alex- Hide quoted text -

- Show quoted text -

I probably spoke too soon (before I had users test the code).
The code works well; however, I'm unable to copy and paste more than
one cell at a time, e.g., copy D12 paste to B1:B2.
I'm also unable to delete any value that's entered into column B.
When I attempt to delete the column or paste as I've described above,
I get a run time error '13' Type Mismatch. Which has something to do
with:
Target.Value = Target.Value + 1

 

[par_60056]

I probably spoke too soon (before I had users test the code).
The code works well; however, I'm unable to copy and paste more than
one cell at a time, e.g., copy D12 paste to B1:B2.
I'm also unable to delete any value that's entered into column B.
When I attempt to delete the column or paste as I've described above,
I get a run time error '13' Type Mismatch. Which has something to do
with:
Target.Value = Target.Value + 1- Hide quoted text -

- Show quoted text -

You can check to make sure that column 2 is in the range modified and
then process each row in the range .

To expand on Rick's Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim CurrentEnableEventsStatus As Boolean
Dim trow As Long
CurrentEnableEventsStatus = Application.EnableEvents
Application.EnableEvents = False
If Target.Column <= 2 And Target.Column + Target.Columns.Count - 1

= 2 Then

For trow = Target.Row To Target.Row + Target.Rows.Count - 1
Cells(trow, 2) = Cells(trow, 2) + 1
Next
End If
Application.EnableEvents = CurrentEnableEventsStatus
End Sub


Peter