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  lt  gt  CVErr xlErrNA   Then  do something End If

User · Accepted Answer

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  lt  gt  CVErr xlErrNA   Then      do something   End If End If

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

Examples related to excel

Examples related to vba