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

Try

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


--

HTH

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


"Markus Scheible" wrote in message
...
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

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

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" wrote in message
...
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

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... :o)

Nevertheless, thanks for the help.

Best regards,

Markus

"Markus Scheible" wrote in message
...
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.

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.

--
Regards,
Tom Ogilvy



"Markus Scheible" wrote in message
...
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

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

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

On Wed, 26 Jan 2005 02:46:43 -0800, "Markus Scheible"
wrote:

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