Replace . with / in Excel giving inconsistent results

I have a data source that uses . as delimiters instead of /.  If I do a manual Find/Replace to swap out the . for /, my dates now look like the dates they're supposed to be.  However, if I do this in macro (and I've tried recording the macro and using the same code) it converts dates to US format and treats Jan 4th as April 1st.

I know plenty of other ways of doing this but for simplicity (and because I've a temp in doing this at the moment) I'd like to just figure out what's wrong with the recorded function and why it behaves differently.  Recorded macro is below, points will go to the answer that requires the least change to what we're doing today...

    Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
BelazirAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Pretty much anything in VBA defaults to US date format. I think your easiest solution is going to be to loop through each cell in the range and use CDate with the Replace function:


Dim rngCell As Range
    For Each rngCell In Selection
        rngCell.Value = CDate(Replace(rngCell.Text, ".", "/"))
    Next rngCell

Open in new window

0
 
patrickabCommented:
Belazir,

Why the odd number of points on offer?

Patrick
0
 
BelazirAuthor Commented:
Patrick - catches people's attention.

Rorya - understand - what I'm trying to get at is that the VBA recording of the find and replace in the GUI doesn't do the same thing, and I don't get why.  I know I can do an iteration through and I will fall back to that if necessary but I'd quite like to get it done using Replace only - if there's a way of doing this.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Rory ArchibaldCommented:
As I said, anything in VBA defaults to US format. It's just the way it is (and always has been), as annoying as that is to those of us in the rest of the world... :)
 
0
 
patrickabCommented:
Belazir,

>catches people's attention.

I guessed as much. However 497 would catch people's attention just as well and would be more generous. Mind you, there's nothing to suggest that you won't up the anti just before awarding the points!

Patrick
0
 
BelazirAuthor Commented:
My admin sorted it by using something in the TextToColumns function I'd never seen before.  Seems to have done the trick.
0
 
BelazirAuthor Commented:
Not quite what I was after but would work.
0
 
Rory ArchibaldCommented:
As a matter of interest how was my answer not what you were looking for? I told you why it was happening...
0
 
BelazirAuthor Commented:
I was looking for a one-liner rorya, that's all.  No idea why TextToColumns works, when Replace goofs up despite it being recorded from a working function.  Just one of those Microsoft oddities I guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.