检查字符串格式

Cheese 发表于 Dev

起司

我有一个循环，循环一个列中的所有条目：

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    indexOfDATE = indexOfDATE + 1
Loop

我需要检查我的tempDate字符串变量是否为dd.MM.yyyy格式，如果不这样做，请显示消息框，而不出现错误？

编辑：

我正在这样做：

Dim indexOfDATE As Integer
indexOfDATE = 3
Do Until indexOfDATE - 1 = Cells(Rows.Count, 2).End(xlUp).Row
    Dim tempDate As String
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", ".")
    tempDate = Replace(tempDate, ".", "/")
    Dim current As Date
    current = Format(CDate(tempDate), "dd/mm/yyyy")
    Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value = current
    indexOfDATE = indexOfDATE + 1
Loop

如您所见，该单元格是一个字符串单元格，我需要确保tempDate字符串的格式正确，然后再进行转换或应用程序掉落。

我想告诉用户消息，在细胞（，）他有不正确的数据

或者也许是转换时的Try Catch块-但是在第一个错误后我未能停止代码执行

悉达思·劳特

试试这个（未测试）

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
    tempDate = Replace(Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2).Value, ",", "/")

    If IsDate(tempDate) Then
        current = Format(CDate(tempDate), "dd/mm/yyyy")
        With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
        End With
        indexOfDATE = indexOfDATE + 1
    End If
Loop

或更短的版本

Dim indexOfDATE As Long
Dim tempDate As String
Dim current As Date

indexOfDATE = 3

With Sheets("Pirmie Ievaddati").Cells(indexOfDATE, 2)
    Do Until (indexOfDATE - 1) = Cells(Rows.Count, 2).End(xlUp).Row
        tempDate = Replace(.Value, ",", "/")

        If IsDate(tempDate) Then
            current = Format(CDate(tempDate), "dd/mm/yyyy")
            .NumberFormat = "dd/mm/yyyy"
            .Value = current
            indexOfDATE = indexOfDATE + 1
        End If
    Loop
End With