Test for Error 2042?

[GBagley]

I've gathered that Error 2042 is the VBA equivalent of #N/A. How do I check for this? I have a worksheet that has a number of formulas that occasionally return #N/A. These have been converted their text values, but I can't seem to get my VBA code to recognize this. I always get a type mismatch.

If cell.offset(0, 12) = "Error 2042" Then 
...
If cell.offset(0, 12) = "" Then 
...
If cell.offset(0, 12) = "#N/A" Then 
...

None of these work. How do I trap these values in an If statement without modifying all my worksheet formulas? I would like to avoid using something like worksheetfunction.isna

 

[shg]

if range("a1").value = cverr(xlerrna) then

or

if worksheetfunction.isna(range("a1").value) then

or, more broadly,

if iserror(range("a1").value) then

 

[GBagley]

Ah, perfect. Thank you!

 

[zeitss]

excellent, i needed this, thanks!

 

[JimSnyder]

Never stops being a resource!

 

[misteroops]

That's work pretty good - Many thank's to you
Alain from Paris

 

[freedive2020]

Hello,
You can check cell.text, text property will return #N/A and any other text shown in cell.

debug.print cell.Text

#N/A

debug.print cell.Value

Error 2042

Good luck