excel - 跳过类型不匹配

Question

在“Value =”行上，循环中有一些实例，我将遇到类型不匹配或单元格为空的情况。

有人可以解释一下如果数据集中有错误时，如何使用错误测试跳过此步骤并继续执行我的循环吗？

谢谢!

Sub ExpDate()

Dim bRow As Double
Dim tRow As Double
Dim lCol As Double
Dim fCol As Double
Dim ListRow As Double

Dim Value As Date

With ThisWorkbook.Worksheets("Canadian")

bRow = Cells(Rows.Count, 5).End(xlUp).row
tRow = 5
fCol = 7

Do While tRow <= bRow
    lCol = Cells(tRow, Columns.Count).End(xlToLeft).Column

    Do While fCol <= lCol


        Value = Cells(tRow, fCol).Value

        ListRow = Cells(Rows.Count, 1).End(xlUp).row + 1
        Cells(ListRow, 1).Value = Value


    fCol = fCol + 1
    Loop

fCol = 7
tRow = tRow + 1
Loop


Range("A5:A1000").RemoveDuplicates Columns:=Array(1, 1), Header:=xlYes

End With

End Sub

Answer 1

一些东西。

您只需要检查单元格是否包含日期。

将Long用作整数变量，而不是Double。

您的With语句是多余的，因为您需要在范围引用之前使用点-我已经添加了它们。

Sub ExpDate()

Dim bRow As Long
Dim tRow As Long
Dim lCol As Long
Dim fCol As Long
Dim ListRow As Long
Dim Value As Date

With ThisWorkbook.Worksheets("Canadian")
    bRow = .Cells(Rows.Count, 5).End(xlUp).Row
    tRow = 5
    fCol = 7

    Do While tRow <= bRow
        lCol = .Cells(tRow, Columns.Count).End(xlToLeft).Column
        Do While fCol <= lCol
            If IsDate(.Cells(tRow, fCol).Value) Then
                ListRow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                .Cells(ListRow, 1).Value = .Cells(tRow, fCol).Value
                fCol = fCol + 1
            End If
        Loop
        fCol = 7
        tRow = tRow + 1
    Loop
    .Range("A5:A1000").RemoveDuplicates Columns:=Array(1, 1), Header:=xlYes
End With

End Sub