lundi 24 février 2020

VBA: IsError in If-Statement

I have the following If-Statement:

If wb.Range("A1").Value = "n/a" Or wb.Range("A1").Value = "na" Then
    wb.Range("A2").Value = "-"
Else
    ...
End

This worked fine until A1 contains #NV (the error, it's not a string) and the code gave me Error 13 (type mismatch). I tried to simply do this:

If IsError(wb.Range("A1").Value) = True Or wb.Range("A1").Value = "n/a" Or wb.Range("A1").Value = "na" Then
    wb.Range("A2").Value = "-"
Else
    ...
End

But again, type mismatch.

If I separate it like this, it works:

If IsError(wb.Range("A1").Value) = True Then
    wb.Range("A2").Value = "-"
ElseIf wb.Range("A1").Value = "n/a" Or wb.Range("A1").Value = "na" Then
    wb.Range("A2").Value = "-"
Else
     ...
End

This lead to a couple of question for me:

  1. Is it possible to handle the error in some way like I tried in the second code snippet, with no elseif and just one If-statement?

  2. Why does If IsError(wb.Range("A1").Value) = True Or wb.Range("A1").Value = "n/a" not work? Does VBA look at all Or conditions simultaneously and one of them returns an error, so the whole If statement does the same?

Aucun commentaire:

Enregistrer un commentaire