VBA查找和替换将日期更改为美国格式

Question

当我通过vba运行查找和替换时，它将我的日期更改为us格式。因此，我有一列日期，但它们都带有我想删除的文本前缀(如发票日期:dd/mm/yyyy)。当我使用Ctrl + F并手动替换时，一切都很棒。删除文本，日期保留其原始格式dd/mm/yyyy。然而，当使用vba这样做时，如果一天少于12(即一年中的月份)，它会将日期更改为mm/dd/yyyy。我已经尝试了许多不同的方法来转换它，但他们似乎都有同样的问题。这是我最近的失败……

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

拜托，在笔记本电脑或我从窗户出去之前，谁来救救我吧。提前感谢

Answer 1

处理日期是非常烦人的。确保使用正确日期的最佳方法是使用dateseries函数，然后按需要格式化输出:

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