|
plus 1
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 |
plus 1
ActiveCell.Value = ActiveCell.Value + 1
-- Regards, Tom Ogilvy "alex" wrote: 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 |
plus 1
On Jul 13, 1:54 pm, Tom Ogilvy
wrote: ActiveCell.Value = ActiveCell.Value + 1 -- Regards, Tom Ogilvy "alex" wrote: 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- Hide quoted text - - 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? |
plus 1
Try it this way....
If ActiveCell.Column = 2 Then ActiveCell.Value = ActiveCell.Value + 1 Rick "alex" wrote in message ups.com... On Jul 13, 1:54 pm, Tom Ogilvy wrote: ActiveCell.Value = ActiveCell.Value + 1 -- Regards, Tom Ogilvy "alex" wrote: 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- Hide quoted text - - 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? |
plus 1
On Jul 13, 3:20 pm, "Rick Rothstein \(MVP - VB\)"
wrote: Try it this way.... If ActiveCell.Column = 2 Then ActiveCell.Value = ActiveCell.Value + 1 Rick "alex" wrote in message ups.com... On Jul 13, 1:54 pm, Tom Ogilvy wrote: ActiveCell.Value = ActiveCell.Value + 1 -- Regards, Tom Ogilvy "alex" wrote: 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- Hide quoted text - - 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?- Hide quoted text - - 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 |
plus 1
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 |
plus 1
On Jul 16, 3:44 pm, "Rick Rothstein \(MVP - VB\)"
wrote: 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 That worked Rick. Thanks for your time. alex |
plus 1
On Jul 17, 6:38 am, alex wrote:
On Jul 16, 3:44 pm, "Rick Rothstein \(MVP - VB\)" wrote: 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 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 D1:D2 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 |
plus 1
On Jul 17, 6:14 am, alex wrote:
On Jul 17, 6:38 am, alex wrote: On Jul 16, 3:44 pm, "Rick Rothstein \(MVP - VB\)" wrote: 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 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 D1:D2 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 |
| All times are GMT +1. The time now is 12:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com