PDA

View Full Version : Immediate window for changing dates



Eddie_th
08-09-2016, 01:22 PM
Hello!

I am implementing a code for the immediate window because I have a costumer who needs to copy and paste dates. But when he does, the dates are modified by 4 years and 1 day. Therefore I want to show him how he can get rid of this.

My idea was to select the cells containing the wrong dates and then use the immediate window. The problem is when we select cells which do not contain dates. It may sound rendundant, but it is not. For example, if we have several cells without dates between our cells with dates, something like this:

01-01-2016
01-01-2016
01-01-2016
Absent
Absent
01-01-2016
Absent
01-01-2016
.
.
.

It is easier to just select all the column instead of using the CTRL+Click method. Therefore, I was using the following code in the immediate window but I get a Next without For error:

For Each cell In Selection:if IsNumeric(cell.Value)=True then cell.Value=cells.Value+1462:Next cell

Pawel
08-09-2016, 01:47 PM
Try this:

Sub ABCD()
Dim cell As Range


For Each cell In Selection: If IsNumeric(cell.Value) = True Then cell.Value = cell.Value + 1462:
Next cell


End Sub

Pawel
08-10-2016, 01:14 AM
Or actually this should do the job:


Sub adddate()
Dim cell As Range
Dim r As Range
Set r = Selection
If IsDate(r) Then
For Each cell In r
Selection.Value = DateAdd("d", 1462, CDate(r))
Next cell
End If
End Sub


Let me know if that helps.

gmaxey
08-10-2016, 07:19 AM
Pawel,

I am by no mean an Excel expert, barely a dabbler, but I couldn't get your method to work. This worked when run within a module:


Public Sub ModifyDateString()
Dim cell As Range
For Each cell In Selection.Cells
If IsDate(cell.Value) Then
cell.Value = DateAdd("d", 1462, CDate(cell.Value))
End If
Next
End Sub

... but it looks like the OP wants to run the code from the immediate window.

Pawel
08-10-2016, 07:24 AM
oohh... Now I'm embarrassed, I missed that info. Don't know why, since it was mentioned like 5 times before. Sorry for trouble.

Paul_Hossler
08-10-2016, 07:29 AM
Since Excel stores dates as a Double and 'Days From', and you're off by 4 years, you might check that the workbooks are both using the same 'Days From' base

16836

I've never had to use the 1904 base, but I understand it's there for compatibility with something called Lotus-123 :*)

gmaxey
08-10-2016, 07:44 AM
No trouble. Actually I was hoping you would recover then go on to tell me how to call ModifyDateString using the Immediate Window. Too much a numb and too embarrassed to ask

Eddie_th
08-10-2016, 08:08 AM
Since Excel stores dates as a Double and 'Days From', and you're off by 4 years, you might check that the workbooks are both using the same 'Days From' base

16836

I've never had to use the 1904 base, but I understand it's there for compatibility with something called Lotus-123 :*)


Yes, thank you. But the problem we have is that there are other dates in other worksheets that will be also modified and we only want the dates that had been pasted to be modified.

Maybe I will just use the normal VBA instead of using the Immediate Window, I will just have to explain that to this person.

Thank you guys for your time, I really appreciate it!