VB Excel 2010 Run-time error '13': Type Mismatch

Question

All I am trying to do is to get a cell to show the percentage increase/decrease between two other cells. The code works fine when there are numerical values in there but after pressing the reset' button (which sets the cell values to "-"), it wont work even though I have worked the "-" scenario into my code:

Private Sub PerChange(ByVal burn1 As Range, burn2 As Range, change As Range)

If burn1.Value And burn2.Value = "-" Then
    change.Value = "-"
Else
    change.Value = (burn1.Value / burn2.Value) - 1
End If

End Sub

It is the bold line where the error occurs even though the values of both are "-"

I also tried swapping the bold line for:

**If IsNumeric(burn1.value and burn2.value) = False**

and it still stopped with a TYpe mismatch on this line, even though the whole point of IsNumeric is to check for non numeric types!!

Please advise as I am tearing my hair out...

Shouldn't it be burn1.Value = "-" And burn2.Value = "-" ? Also If IsNumeric(burn1.value and burn2.value) = False is valid but definetly not what you want. Try If Not IsNumeric(burn1.value) and Not IsNumeric(burn2.value) — z̫͋
Thanks for your help. Can i Just ask though, you said my statement was valid but not waht I want. Why is that? Is it because IsNumeric() can only take one value? — Braide
The thing is that IsNumeric(burn1.value and burn2.value) says that you want to know if the expression burn1.value and burn2.value is a numeric value. Using the And operator on variant values is error prone and not very intuitive, and definitely not what you are trying to achieve. For example, if one of the cell value is a string which cannot be converted to a number (ex. "abc"), you will get an error. Or if your cells are empty then Empty and Empty will actually return zero which is a numerical value. So yes, use IsNumeric on expression you can predict, like a single variable — z̫͋

Gary's Student Gary's Student · Accepted Answer · 2014-05-30T12:13:55

This will work:

Private Sub PerChange(ByVal burn1 As Range, burn2 As Range, change As Range)
    If burn1.Text = "-" And burn2.Text = "-" Then
        change.Value = "-"
    Else
        change.Value = (burn1.Value / burn2.Value) - 1
    End If
End Sub

tested with:

Sub MAIN()
    Call PerChange(Range("A1"), Range("A2"), Range("B1"))
End Sub

VB Excel 2010 Run-time error '13': Type Mismatch

1 Answers