时间戳

首页 > 解决方案 > 跳过类型不匹配

excel - 跳过类型不匹配

问题描述

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

标签： excelvbaloopserror-handlingskip

解决方案

一些东西。

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

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

推荐阅读