VBA Find & Replace Changes Date To US Format-CodePudding

when I run find and replace through vba it changes my dates to us format. So I have a column of dates, but they are all prefixed with text that I want to remove (like so Invoice Date:dd/mm/yyyy). When I use Ctrl F and replace manually, it's all great. Removes the text, the date remains in it's original format dd/mm/yyyy. However, when using vba to do this it changes the dates to mm/dd/yyyy if the the day is less than 12 (ie months in a year). I've tried a number of different methods to convert it but they all seem to have the same problem. Here is my latest failure...

Sub DateConvert()

    Sheets("Sheet1").Select

    Dim strValue As String
    Dim RowCount As Integer
    Dim x As Integer
    Dim DateValue As Date

    RowCount = WorksheetFunction.CountA(Range("C1", Range("C1").End(xlDown)))

    For x = 2 To RowCount
        'changes cell value to a string
        strValue = Cells(x, 3).Value
        'removes unwanted text
        Cells(x, 3).Replace _
        What:="Invoice Date:", Replacement:=""
        'changes to string to desired date format
        DateValue = Cells(x, 3).NumberFormat = "dd/mm/yyyy"
        
    Next x
       

End Sub

Please, someone spare me this misery before either the laptop or me go out the window. Thanks in advance

CodePudding user response：

Dates are extremely annoying to work with. Your best bet for making sure you're working with the correct date is to use the DateSerial function, and then format the output as desired:

Sub DateConvert()
    
    Dim ws As Worksheet:    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    Dim rData As Range:     Set rData = ws.Range("C2", ws.Cells(ws.Rows.Count, "C").End(xlUp))
    If rData.Row < 2 Then Exit Sub  'No data
    
    'Load range data into an array
    Dim aData() As Variant
    If rData.Cells.Count = 1 Then
        ReDim aData(1 To 1, 1 To 1)
        aData(1, 1) = rData.Value
    Else
        aData = rData.Value
    End If
    
    'Loop over array and perform conversion
    Dim aDateValues As Variant
    Dim i As Long
    For i = 1 To UBound(aData, 1)
        aDateValues = Split(Replace(aData(i, 1), "Invoice Date:", vbNullString), "/")   'Remove the extra text and pull the date values
        aData(i, 1) = DateSerial(aDateValues(2), aDateValues(1), aDateValues(0))        'Use DateSerial to guarantee correct date
    Next i
    
    'Output results to sheet with desired date format
    With rData
        .Value = aData
        .NumberFormat = "dd/mm/yyyy"
    End With
    
End Sub