Replace 1

[thelos]

hi there again,


i am trying to replace a"." with a"/" in column A, i have used the recorder and it gave me this code:

Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _ReplaceFormat:=False



when i use the code in a macro the code moves some of my values around (it is dates in column a)
for example if i have "11.07.05" in some cases it returns "07/11/05"


if i stretch my column out the dates that are wrong have moved as shown below




Work Date
23/07/2005
07/11/2005
14/07/2005
14/07/2005
14/06/2005
16/08/2005
18/08/2005
06/09/2005
07/12/2005
13/07/2005
08/08/2005
08/09/2005

any help would be great guys thanks

 

[PHV]

So, in column A you had text values formatted as dd.mm.yyyy you want to convert to dates ?
Instead of your Search/Replace code you may try this:

Dim c As Range, a, s As String
For Each c In Range("A:A")
  s = c.Text
  a = Split(s, ".")
  If UBound(a) = 2 Then
    c.Value = DateSerial(a(2), a(1), a(0))
  End If
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886