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]

enter 1 in a cell copy it
select the cell with the date
paste special add
this should increase the day by one, assuming what was pasted was a date and
not text that looked like a date.

 

[Gord Dibben]

Enter 1 in a cell.

Copy that cell.

Select the date cell.

Paste Special>Add>OK>Esc.

Re-format as date.


Gord Dibben MS Excel MVP

 

[alex]

Enter 1 in a cell.

Copy that cell.

Select the date cell.

Paste Special>Add>OK>Esc.

Re-format as date.

Gord Dibben MS Excel MVP







- Show quoted text -

Thanks (to both) for your help. Your suggestions obviously worked.
Would you happen to know the VBA code to accomplish the same task,
without the extra step?
I'd like users to simply paste in the date and have said date
automatically increase by one.

alex

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value + 1
.NumberFormat = "dd mm yyyy"
Application.EnableEvents = True
End With
End Sub

This is sheet event code. Right-click on the sheet tab and copt/paste the code
into that sheet module.

Gord

 

[alex]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
With Target
.Value = .Value + 1
.NumberFormat = "dd mm yyyy"
Application.EnableEvents = True
End With
End Sub

This is sheet event code. Right-click on the sheet tab and copt/paste the code
into that sheet module.

Gord





- Show quoted text -

Thanks for your help Gord. This code works; however, only when I type
the values and not when they're pasted from another application,
including Excel.

alex

 

[Gord Dibben]

I tested before posting and tested again just now.

Works one cell at a time as you paste a date to each cell.

Are you trying to paste a range of cells?


Gord

 

[alex]

I tested before posting and tested again just now.

Works one cell at a time as you paste a date to each cell.

Are you trying to paste a range of cells?

Gord







- Show quoted text -

Gord,

I am pasting a range of cells, sometimes hundreds, but only one
column.

alex

 

[Gord Dibben]

You don't say which column but how about column A?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rcell As Range
If Target.Column <> 1 Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rcell In Selection
If rcell.Value <> "" Then
With rcell
.Value = .Value + 1
.NumberFormat = "dd mm yyyy"
End With
End If
Next
endit:
Application.EnableEvents = True
End Sub


Gord

 

[alex]

You don't say which column but how about column A?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rcell As Range
If Target.Column <> 1 Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rcell In Selection
If rcell.Value <> "" Then
With rcell
.Value = .Value + 1
.NumberFormat = "dd mm yyyy"
End With
End If
Next
endit:
Application.EnableEvents = True
End Sub

Gord







- Show quoted text -

That worked Gord; thanks for all your help.

alex

 

[Gord Dibben]

Thanks for the feedback

Glad it worked out.


Gord