CODEWITHSUNDEEP
vbaexcelreplace
Dmitry Vasilyev
Dmitry Vasilyev

Reputation: 85

find & replace vba changes format of date

I have a range of cells with dates, that contain values that have to be replaced. Once I do that using general Find & Replace - it works smoothly. When I do it in VBA, the formatting of the cells is changed and dates are converted to a wrong ones, e.g. 12/10/2015 (12 October), but become 10/12/2015 (10 December). Here's the code: 

Sub find_replace()
ActiveSheet.Range("T1", Range("T1").End(xlToRight).Offset(0, -2)).Select
With Selection
.NumberFormat = "dd/mm/yyyy"
.Replace What:="P.", Replacement:="", LookAt:=xlPart,     SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False,   ReplaceFormat:=False
.Replace What:=".", Replacement:="/", LookAt:=xlPart,    SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False,    ReplaceFormat:=False
End With
End Sub

Please be so kind as to advise how to get rid of this annoying issue. Thanks.

Upvotes: 2

Views: 5437

Answers (3)

Rory
Rory

Reputation: 34075

Here's a loop sample per my comment earlier:

Sub ParseDates()
    Dim vData
    Dim rData                       As Range
    Dim x                           As Long
    Dim y                           As Long
    Dim stemp                       As String
    Set rData = ActiveSheet.Range("T1", Range("T1").End(xlToRight).Offset(0, -2))
    vData = rData.Value2
    For x = LBound(vData, 1) To UBound(vData, 1)
        For y = LBound(vData, 2) To UBound(vData, 2)
            stemp = Replace$(Replace$(vData(x, y), "P.", ""), ".", "/")
            If IsDate(stemp) Then vData(x, y) = CDate(stemp)
        Next y
    Next x
    rData.Value2 = vData

End Sub

Upvotes: 0

Fabrizio
Fabrizio

Reputation: 662

Date() I'm Italian and always quarrel with the date.. If the date that you want convert is alway like P.01.10.2015 to 1th Oct try the code below, otherwise change the start/end of right(x,x),mid(x,x,x)

Sub find_replace()
Dim cell As Range
Dim yr As String, mnt As String, dy As String
Dim d As Date
For Each cell In ActiveSheet.Range("T1", Range("T1").End(xlToRight).Offset(0, -2))
yr = Right(cell, 4)
mnt = Mid(cell, 6, 2)
dy = Mid(cell, 3, 2)
d = DateSerial(yr, mnt, dy)
Range(cell.Address) = d
Next cell
End Sub

Upvotes: 0

BruceWayne
BruceWayne

Reputation: 23285

It's due to the .NumberFormat. Try changing that to .NumberFormat = "mm/dd/yyyy"

Edit: Also, maybe just comment out/remove that line, if it's totally unnecessary.

Edit2: The above isn't the issue. What if, after you have run the macro and have the dates in dd/mm/yyyy format, you do Text to Columns? Highlight the dates, go to Data--> Text to Columns, choose "Delimited" and leave as "Tab", but in the next screen (step 3 of 3), choose "Column data format" as "Date: MDY", and click "Finish".

Upvotes: 1

Related Questions