IsError Function

[Markus Scheible]

Hi again,

why doesn't work this:

If IsError(.Range("1").SpecialCells(xlCellTypeBlanks).Cells
(1)) = False Then

When I run the macro I get the error note "no cells were
found. run-time error 1004" because the range is filled up
and the macro stops instead of running the else command

Thanks a lot for your help and best regards

Markus

 

[Bob Phillips]

Try

If IsError(.Range("1:1").SpecialCells(xlCellTypeBlanks).Cells
(1)) = False Then


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Markus Scheible]

Hi Bob,

If IsError(.Range("rng:rng").SpecialCells (xlCellTypeBlanks).Cells
(1)) = False Then

that doesn't work either... same error: "no cells were
found" - the point is: I know that no cells were found -
therefor I used the if command... the thing is that excel
seems to not accept this error within the iferror
function...

Best regards,

Markus

 

[Bob Phillips]

I think the error is being generated before the IsError kicks in.

Try a different tack

Set myRng = Nothing
On Error Resume Next
Set myRng = (.Range("rng:rng").SpecialCells(xlCellTypeBlanks).Cells(1))
On Error Goto 0
If myRng Is Nothing Then
... this is the False path
End If
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Markus Scheible]

Hi Bob,

I think the error is being generated before the IsError

kicks in.


Could be, but the Debugger stops at that line, so that I
could not imagine how the error would have been generated
before... plus: that line is the first time in the whole
macro that I use the specialcells(xlcelltypeblanks)
command so I doubt that excel produces the error before...

Try a different tack

Set myRng = Nothing
On Error Resume Next
Set myRng = (.Range("rng:rng").SpecialCells (xlCellTypeBlanks).Cells(1))
On Error Goto 0
If myRng Is Nothing Then
... this is the False path
End If
--

I did that, and it worked... )

Nevertheless, thanks for the help.

Best regards,

Markus

 

[Bob Phillips]

Could be, but the Debugger stops at that line, so that I
could not imagine how the error would have been generated
before... plus: that line is the first time in the whole
macro that I use the specialcells(xlcelltypeblanks)
command so I doubt that excel produces the error before...

Understood, I meant that parsing that statement executed the range setting
part and fell over there before the IsError got its results. Which is why I
went the way I suggested.

 

[Tom Ogilvy]

Iserror only responds to Excel style errors such as

? cverr(xlErrNa)
Error 2042
? iserror(cvErr(xlErrNA))
True

this is equivalent to
#N/A

on the worksheet.

Special Cells Raises a Trappable error when it fails. This is handled as
Bob has shown you.

 

[Markus Scheibleq]

Hi Tom,

Special Cells Raises a Trappable error when it fails. This is handled as
Bob has shown you.

thanks for the info.

Best regards

Markus

 

[Myrna Larson]

Note that the way you have written the statement, what you as asking for is
whether the VALUE in the first blank cell is an error value -- which of course
is nonsense: if the cell is blank, it can't contain an error value.

When there are no blank cells, you get a run-time error at the point where VBA
is trying to create a temporary range variable that contains no cells.

If what you want to know is whether there ARE any blank cells, and do that
without raising an error, you could use

If Application.COUNTBLANK(Rng) > 0 Then

or

If Application.COUNTA(Rng) <> Rng.Cells.Count Then

or, with error trapping

On Error Resume Next
Set Rng2 = Rng.SpecialCells(xlCellTypeBlanks)
If Err.Number <> 0 Then 'no blanks