Why appears the "type mismatch" error in if condition in VBA?-CodePudding

Here is my code. "For rowCycle = 4" it's because I have double table header and indent from the border in one cell. The beginning of the required lines comes from the fourth line.

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

I tried this and got the "Type mismatch error" with the line 16 (If condition)

I don't know why, but when I decided to debug in "Watches" I see: enter image description here

Why it is so, who knows?

CodePudding user response：

A cell with the value #N/A will throw an exception when you try to match it with a literal string. There's three ways you can check for an exception.

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

You'll also need to test this before you try your other conditions.

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

CodePudding user response：

Handling Dates

In a nutshell, if you're testing a cell for a date, use the IsDate function i.e. if it's a date, it cannot be empty, blank, or an error, or...
Usually, you would want to open one workbook at a time, process it, save it (or not), and close it i.e. having the same code for both workbooks inside another outer loop. In this case, this is achieved by introducing three arrays at the beginning of the code.

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