Checking for #N/A in Excel cell from VBA code

Question

I'm iterating through a range of cells which hold numbers with 2 decimal places. I need to check if the cell holds '#N/A', and if it does, I need to skip it. The problem is, when a cell holds a valid number, my if condition below throws a 'Type mismatch error'. How can I avoid this?

If (ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value <> CVErr(xlErrNA)) Then
'do something
End If

Sorry, asked the question hastily. Casting LHS of expression as CVErr fixes it. — Ayush
– Ayush, Commented Feb 28, 2011 at 14:44
You can also use IsError(rg) if you're not concerned about the specific type of error. — D_Bester
– D_Bester, Commented Jul 16, 2014 at 18:03

user637663 · Accepted Answer · 2011-02-28 15:01:35Z

27

First check for an error (N/A value) and then try the comparisation against cvErr(). You are comparing two different things, a value and an error. This may work, but not always. Simply casting the expression to an error may result in similar problems because it is not a real error only the value of an error which depends on the expression.

If IsError(ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value) Then
  If (ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value <> CVErr(xlErrNA)) Then
    'do something
  End If
End If

edited Feb 28, 2011 at 15:01

answered Feb 28, 2011 at 14:52

user637663

2863 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

jtolle Over a year ago

There is also Application.IsNA, which you would use like: 'If Application.IsNA(ActiveWorkbook.Sheets("Publish").Range("G4").offset(offsetCount, 0).Value) Then'

pablete Over a year ago

Could not find the Application.IsNA() in excel 2010 @jtolle

jtolle Over a year ago

@pablete: It's there! You can use either Application.IsNA() (which will not show up in VBA's little autocomplete suggestion), or Application.WorksheetFunction.IsNA(), which will. For example, try this in the Immediate window: ?application.isna([na()]). You should get back True.

pablete Over a year ago

Ahh, ok, now it works (does not show on the auto complete feature), thanks a lot.

Hristo Over a year ago

IMO, as the N/A error is part of all errors found by IsError, the first command suffice the asked need. Only, it shall be negated to skip erroneous rows - If Not IsError(...) Then 'do something.

Collectives™ on Stack Overflow

Checking for #N/A in Excel cell from VBA code

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related