Convert text date to true date. vba help! Why this code doesn't work?

[Xceller]

VBA gurus - I have some dates in text fomat on a worksheet that I need to convert them to true dates(Value). Here some sample of the text dates: '12/31/2011; Blank space12/31/2011. Thanks for your help in advance. Here is code:

Sub CnvtTextDate()

For Each Rng In Selection

Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Cells.Replace What:=",", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Trim (Rng)

With Rng

.NumberFormat = "mm/dd/yy;@"


End With

Next Rng

End Sub

 

[Warship]

this works for me:

Sub CnvtTextDate()
    Dim rng As Variant
    For Each rng In Selection
        rng.Value = Trim(rng)
        rng.NumberFormat = "mm/dd/yy;@"
    Next rng
End Sub

 

[bstory84]

slightly confused. do u have a single date u need coverted or 2 dates in the same cell?

 

[Xceller]

slightly confused. do u have a single date u need coverted or 2 dates in the same cell?

Just one date within the same cell.

 

[Xceller]

Warship - Appreciate your help! It works like charm!

 

[Xceller]

Warship - I attached a command button to your code. For whatever reason I have to click the button 2 times to convert the dates to value dates. I tried stepping through the code, I think the 1st click it trims the the text string and the 2nd click converts the text string to value. Is there a way to do this in click of the button?

 

[Warship]

I don't get that behavior with a Command Button.

The code is looping through the current selection.
Do you have anything else effecting the selection before/as it runs?

Maybe it would be better to set the range we look thru using a different method and not use Selection?