How to handle #error when testing cell with Len() function

[tinkythomas]

Hi,

How can I handle cells that could contain #REF, #N/A etc when testing cell value for length?

I get runtime error 13 (type mismatch) when a cell contains an #error as a result of a formula. I'm using a statement in vba like the following...

If Len(Cells(1, 1)) = 0 Then Do Something

Regards,

 

[Tuelor]

You can test the cell for an error using the following (or its iferror - not sure as on my phone)

Application.WorksheetFunctions.IsError()

D

 

[tinkythomas]

Hi,

Thanks for the reply. Sorry should have mentioned that I had already tried that with...

If Len(Cells(1, 1)) = 0 Or WorksheetFunction.IsError(Cells(1, 1)) Then Do Something

but it still get the runtime error 13.

Thanks,

 

[Tuelor]

I would try the IsError first, then check the length as a second if statement.

Otherwise we can create a range variable, set it to the cell in question, and test if that's not null.

Or skip errors altogether with, On error resume next

D

 

[tinkythomas]

Thanks for your help. Putting the IsError first worked with the Len() check as an ElseIf. Would it be possible to use the single line form with an ElseIf? I tried it but couldn't get it to work, it just wouldn't compile.

The single line form works with just 1 condition but not with an ElseIf???

Thanks,