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
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