From VBA, how do I test for a cell with value #N/A

  • Thread starter Thread starter hmmm...
  • Start date Start date
H

hmmm...

For example, Cells(1,8) contains #N/A. The following VBA statement:

If Worksheets("Sheet1").Cells(1, 8).Value = "" Then

gives a type mismatch error.

Thanks for any suggestions.
 
You could use the .text property:
If Worksheets("Sheet1").Cells(1, 8).Text = "" Then

Or you could use:

if iserror(worksheets("sheet1").cells(1,8).value) then
'do something with the error
else
if worksheets("sheet1").cells(1,8).Value = ""
....

You might even be able to use:

if isempty(worksheets("sheet1").cells(1,8)) then
But if that cell contains a formula that evaluates to "", it's not empty.
 
I figured it out. I was going to test against cvErr(xlErrNA), which
succeeds when the cell has #N/A, but failed when the cell contained data, so
I ended up using isError(). Thanks.
 
Try this, it worked for me.

If Worksheets("Sheet1").Range("B3").Value = "" Then

Hope it helps!
Nyle
 
Back
Top