Excel VBA: Type Mismatch

Question

I have been using the code outlined below; however, every once in a while, the debugger with throw a "type mismatch" error. This code simply compares two worksheets (A and B, they are in the same workbook) and highlights the differences on worksheet B (by highlighting cells yellow). How can I get rid of the "type mismatch" error?

Also, it is important to note that the worksheets are in the exact same format and each column's data starts in cell 12.

Any help would be much appreciated.

Option Explicit

Sub Compare_Tracker()
    Dim varSheetA As Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long
    strRangeToCheck = "A12:K150"
    varSheetA = Worksheets("Main").Range(strRangeToCheck)
    varSheetB = Worksheets("Discrepancy Compare").Range(strRangeToCheck) ' or whatever your other sheet is.
    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
           If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                ' Cells are different.
                ' Highlight different cells yellow.
                Worksheets("Discrepancy Compare").Cells(iRow + 11, iCol).Interior.ColorIndex = 36
            End If
        Next iCol
    Next iRow
End Sub

If I had to guess, you're getting an error when the value in the cell is an error (like `#N/A` or `#REF`). If that's the case, you could basically replace errors with 0s or something and then it should still execute... — brianyates, May 24 '18 at 19:05
@ JohnyL The line that errors out is `If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then` — Jay.Kel, May 24 '18 at 19:09
If BYates' guess is correct, you could use `If Not Application.WorksheetFunction.IsError(varSheetA(iRow, iCol).Value)` to skip over the error values — cybernetic.nomad, May 24 '18 at 19:11
So I'm looking back at the wookbooks in which I had error running the macro. It appears that none of the cells are #N/A or #REF. — Jay.Kel, May 24 '18 at 19:19
if you debug on the error and hover the mouse cursor over the variable names, you will see their current values, looking at `iRow` and `iCol` will tell you which cells are causing the problem can you let us know what is in those cells? — cybernetic.nomad, May 24 '18 at 19:27
I am a complete idiot. They is an #N?A error that I overlooked. BYates' suggestion was correct. Thanks all!! — Jay.Kel, May 24 '18 at 19:36

score 0 · Answer 1 · answered May 25 '18 at 00:48

Well, here goes a lesson on understanding of Type for Variables.. You are getting Type Mismatch error because for some ranges the types cannot be matched. I am giving a modified version of your code to understand the concept of Type in a better way and to resolve the issue.

For eg. a string can not be compared to a number. An error cannot be compared either string or number.

In the solution below, we will first compare the type using VarType and after their type matches successfully then we will perform second level match.

Option Explicit

Sub Compare_Tracker()
    Dim varSheetA As Variant, varSheetB as Variant
    Dim strRangeToCheck As String
    Dim iRow As Long
    Dim iCol As Long
    strRangeToCheck = "A12:K150"
    varSheetA = Worksheets("Main").Range(strRangeToCheck)
    varSheetB = Worksheets("Discrepancy Compare").Range(strRangeToCheck) ' or whatever your other sheet is.
    For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
        For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
           If VarType(varSheetA(iRow, iCol)) = VarType(varSheetB(iRow, iCol)) Then
               If varSheetA(iRow, iCol) <> varSheetB(iRow, iCol) Then
                   ' Cells are different.
                   ' Highlight different cells light yellow.
                   Worksheets("Discrepancy Compare").Cells(iRow + 11, iCol).Interior.ColorIndex = 36
                 End If
           Else
               ' Cells Type are different.
               ' Highlight different cells light red.
               Worksheets("Discrepancy Compare").Cells(iRow + 11, iCol).Interior.ColorIndex = 38
           End If
        Next iCol
    Next iRow
End Sub

Excel VBA: Type Mismatch

1 Answers1

Linked