Excel VBA - 日期替换

Question

我将使用VBA在MS Excel中将整行的数据格式从YY-MM-DD的文本格式替换为DD-MM-YY。我尝试删除开头的撇号，但是日期21-04-02被识别为21.04.2002而不是02.04.2021，因此需要在整个单元格内替换字符。

在下面的代码中，我需要为每个单元格添加替换部分。

Sub DateSub()
   Dim strInput As String, strOutput As String
   Dim LastRowcheck As Long, n1 As Long, rowschecktodelete As Long

  LastRowcheck = Sheets("T1").Range("B" & Rows.Count).End(xlUp).Row

  For n1 = 2 To LastRowcheck
    With Worksheets("Sheet1").Cells(n1, 2)
     'HERE SHOULD BE REPLACING PART
        Sheets("T1").Cells(n1, 2).Select
        Selection.NumberFormat = "yy-mm-dd"
   End With
  Next n1
    Application.DisplayAlerts = False

End Sub 

- stasser

---

一个公式可以解决这个问题。假设你的日期在A1单元格中为'21-04-02。将公式=DATE(2000+LEFT(A1,2),MID(A1,4,2),RIGHT(A1,2))放置在B1单元格中。如果你仍然想使用VBA，那么可以在其中使用相同的逻辑。 - Siddharth Rout

我相信那会起作用，但我是VBA新手，我错过了一些使它正常工作的东西，我想以这种方式替换列B中的每个单元格。Sheets("T1").Cells(n1, 2) = DATE(2000+LEFT(nl,2),MID(A1,4,2),RIGHT(nl,2)) - stasser

Answer 1

   Dim strInput As String, strOutput As String
   Dim LastRowcheck As Long, n1 As Long, rowschecktodelete As Long

  LastRowcheck = Sheets("T1").Range("B" & Rows.Count).End(xlUp).Row

  For n1 = 2 To LastRowcheck
    With Worksheets("T1").Cells(n1, 2)
     'HERE SHOULD BE REPLACING PART
        d1 = Sheets("T1").Cells(n1, 2).Value
        Sheets("T1").Cells(n1, 2).Value = Mid(d1, 7, 2) + "-" + Mid(d1, 4, 2) + "-" + Mid(d1, 1, 2)
        
        Sheets("T1").Cells(n1, 2).Select
        'Selection.NumberFormat = "yy-mm-dd"
        Selection.NumberFormat = "dd-mm-yy"
        
   End With
  Next n1
    Application.DisplayAlerts = False

- Emmanuel Donald

Answer 2

如果您需要使用VBA，则可以像下面所示一样将整个范围转换为一个循环而不是逐个循环。

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sAddr As String
    Dim lRow As Long
    
    Set ws = Worksheets("Sheet1")
    
    With ws
        lRow = .Range("B" & .Rows.Count).End(xlUp).Row
        
        Set rng = Range("T1:T" & lRow)
        sAddr = rng.Address

        rng = Evaluate("index(DATE(2000+LEFT(" & sAddr & _
                              ",2),MID(" & sAddr & _
                              ",4,2),RIGHT(" & sAddr & _
                              ",2)),)")
                              
        rng.NumberFormat = "DD-MM-YY"
    End With
End Sub

要了解如何操作，请参见无需遍历所有单元格即可将整个范围转换为大写字母

实例演示

- Siddharth Rout

Answer 3

你可以使用“文本分列”方法。

例如：

Option Explicit
Sub convDates()
    Const convCol As Long = 1
    Dim WS As Worksheet, Rng As Range
    
'change sheetName, convCol and destination to your specifications if this works for you.
    
Set WS = Worksheets("sheet5")
With WS
    Set Rng = Range(.Cells(1, convCol), .Cells(.Rows.Count, convCol).End(xlUp))
    Rng.TextToColumns _
        Destination:=.Cells(1, convCol + 1), _
        DataType:=xlDelimited, _
        textqualifier:=xlTextQualifierDoubleQuote, _
        Consecutivedelimiter:=True, _
        Tab:=False, _
        semicolon:=False, _
        Space:=False, _
        other:=False, _
        fieldinfo:=Array(1, xlYMDFormat)
End With
End Sub

在上面的代码中，convCol 是您希望转换的列（在此示例中为A列），请注意我们将结果写入左侧的一列（请参见 Destination:= 参数）。

如果您对此的工作方式感到满意，可以将convCol更改为您感兴趣的列（可能是B列或2），并通过从Destination:=参数中删除+1来覆盖原始数据。

- Ron Rosenfeld