excel VBA中if条件为什么会出现“类型不匹配”错误？

ehxuflar 于 2023-01-14 发布在其他

关注(0)|答案(2)|浏览(532)

下面是我的代码。“For rowCycle = 4” 这是因为我有双表头，并且在一个单元格中从边框开始缩进。所需行的开头来自第四行。

Sub ColumnsFind()

Dim ReqWorkbook1 As Workbook

Dim ReqWorkbook2 As Workbook

Set ReqWorkbook1 = Workbooks.Open("C:\Users\ignatevaeg\Excel\VBA\Book1.xlsx")
Set ReqWorkbook2 = Workbooks.Open("C:\Users\ignatevaeg\Excel\VBA\Book2.xlsx")

Dim rowCycle, secondCycle

secondCycle = 1

For rowCycle = 4 To ThisWorkbook.Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Row
    
    If ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle).NumberFormat = "dd-mm-yyy" And ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle).Value <> "" And ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle).Value <> "#Н/Д" Then
    
        ReqWorkbook1.Sheets("Sales").Range("A" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("B" & rowCycle).Value
        ReqWorkbook1.Sheets("Sales").Range("B" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("C" & rowCycle).Value
        ReqWorkbook1.Sheets("Sales").Range("C" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("D" & rowCycle).Value
        ReqWorkbook1.Sheets("Sales").Range("D" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle).Value
        ReqWorkbook1.Sheets("Sales").Range("E" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AU" & rowCycle).Value
        ReqWorkbook1.Sheets("Sales").Range("F" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AV" & rowCycle).Value
        secondCycle = secondCycle + 1
    
    End If

Next rowCycle

For rowCycle = 4 To ThisWorkbook.Sheets("Sales").Cells(Rows.Count, 1).End(xlUp).Row
    
    If ThisWorkbook.Sheets("Sales").Range("AN" & rowCycle).NumberFormat = "dd-mm-yyy" And ThisWorkbook.Sheets("Sales").Range("AN" & rowCycle).Value <> "" And ThisWorkbook.Sheets("Sales").Range("AN" & rowCycle).Value <> "#Н/Д" Then
    
        ReqWorkbook2.Sheets("Sales").Range("A" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("B" & rowCycle).Value
        ReqWorkbook2.Sheets("Sales").Range("B" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("C" & rowCycle).Value
        ReqWorkbook2.Sheets("Sales").Range("C" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("D" & rowCycle).Value
        ReqWorkbook2.Sheets("Sales").Range("D" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AN" & rowCycle).Value
        ReqWorkbook2.Sheets("Sales").Range("E" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AO" & rowCycle).Value
        ReqWorkbook2.Sheets("Sales").Range("F" & secondCycle).Value = ThisWorkbook.Sheets("Sales").Range("AP" & rowCycle).Value
        secondCycle = secondCycle + 1
    
    End If

Next rowCycle

End Sub

我尝试了这个方法，并在第16行（If条件）中得到了“类型不匹配错误”
我不知道为什么，但是当我决定在“手表”中调试时，我看到：

为什么会这样，谁知道呢？

excel

来源：https://stackoverflow.com/questions/75079315/why-appears-the-type-mismatch-error-in-if-condition-in-vba

2条答案

按热度按时间

dffbzjpn1#

当你试图将一个值为#N/A的单元格与一个字符串匹配时，它会抛出一个异常。有三种方法可以检查异常。

With ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle)
    TypeName(.Value) =  "Error"
    .Value = CVErr(xlErrNA)
    IsError(.Value)
End With

您还需要在尝试其他条件 * 之前 * 进行测试。

With ThisWorkbook.Sheets("Sales").Range("AT" & rowCycle)
    If Not .Value = CVErr(xlErrNA) Then
        If .NumberFormat = "dd-mm-yyy" And .Value <> "" Then
            ...
        End If
    End If
End With

赞(0）回复(0）举报 2023-01-14

jfewjypa2#

处理日期

简而言之，如果你在测试一个单元格中的日期，使用IsDate函数，也就是说，如果它是一个日期，它不能是空的，空白的，错误的，或者...
通常，您希望一次打开一个工作簿，处理它，保存它（或不保存），然后关闭它，也就是说，在另一个外部循环中为两个工作簿使用相同的代码。在本例中，这是通过在代码开头引入三个数组来实现的。

Option Explicit

Sub ExportData()

    Dim dNames(): dNames = Array("Book1.xlsx", "Book2.xlsx")
    Dim sDateCols(): sDateCols = Array("AT", "AN")
    Dim sCols(): sCols = Array("AT:AC", "AN:AP")

    Dim swb As Workbook: Set swb = ThisWorkbook
    Dim sws As Worksheet: Set sws = swb.Sheets("Sales")
    Dim srg1 As Range: Set srg1 = sws.Columns("B:D")
    Dim slRow As Long: slRow = sws.Cells(sws.Rows.Count, "A").End(xlUp).Row
    
    Dim dwb As Workbook, dws As Worksheet, drg As Range
    Dim srg2 As Range, sr As Long, n As Long, sdCol As String
    
    For n = LBound(dNames) To UBound(dNames)
        Set dwb = Workbooks.Open("C:\Users\ignatevaeg\Excel\VBA\" & dNames(n))
        Set dws = dwb.Sheets("Sales")
        Set drg = dws.Range("A1:C1")
        Set srg2 = sws.Columns(sCols)
        sdCol = sDateCols(n)
        For sr = 4 To slRow
            If IsDate(sws.Cells(sr, sdCol).Value) Then
                drg.Value = srg1.Rows(sr).Value
                drg.Offset(, 3).Value = srg2.Rows(sr).Value
                Set drg = drg.Offset(1)
            End If
        Next sr
        'dwb.Close SaveChanges:=True
    Next n
 
    MsgBox "Data exported.", vbInformation
 
End Sub

赞(0）回复(0）举报 2023-01-14

我来回答

excel VBA中if条件为什么会出现“类型不匹配”错误？

2条答案

处理日期

相关问题

热门标签

最新问答