Type mismatch in Select Case with Variants of subtype Error

Question

The range indicated in the code below contains a VLOOKUP formula. The code is meant to notify the user if VLOOKUP returns a #N/A.

However, I get a Type Mismatch error on the Case CVErr(xlErrNA) line below. Why? How can I fix this?

Private Sub Worksheet_Calculate()
  Dim c As Range
  For Each c In Range("N7:N13,N30:N36,N53:N59,N85:N91,N108:N114,N137:N137")
     Select Case [c]
             Case CVErr(xlErrNA)
                   MsgBox "hello"          
      End Select
  Next c
End Sub

The given ranges has Vlookup formula which bring the result from another sheet, I want user to add product if vlookup given #N/A error — Abbas
@DavidZemens, thank you for information. Again I am learning something new here in SO :) — lowak

Jean-François Corbett Jean-François Corbett · Accepted Answer · 2014-08-07T14:39:37

Apparently, Variants of subtype Error (as are ouput by CVErr) don't like to be compared to any other type. When that happens, a type mismatch is thrown. You will get this error as soon as c doesn't contain an error code.

So you have to split your Select Case statement in two: one part to deal with error codes, the other to deal with everything else. This works for me:

Dim c As Range
For Each c In Range("N7:N13,N30:N36,N53:N59,N85:N91,N108:N114,N137:N137")
    If IsError(c.Value) Then
        'Deal with error codes
        Select Case c.Value
            Case CVErr(xlErrNA)
                MsgBox "#N/A"
            Case CVErr(xlErrDiv0)
                MsgBox "Divide by zero."
                'etc.
        End Select
    Else
        'Deal with everything else
        Select Case c.Value
            Case 1
                MsgBox "1"
            Case "a"
                MsgBox "a"
                'etc.
        End Select
    End If
Next c

Type mismatch in Select Case with Variants of subtype Error

1 Answers