XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select

[Trevor Williams]

Hi All

I'm using a bit of code to copy formulae into blank cells using
'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error on
the occassions when there are no blank cells. I've tried using an On Error
statement but doesn't seem to work.

Any ideas how I get around the error msg?
Code below

Regards
Trevor Williams

Range("A1").CurrentRegion.Select
On Error GoTo CleanUp
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

also tried:

If Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select = True Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

 

[Peter T]

Your first method looks OK assuming code also includes an error handler, but
difficult to say based on the little you posted. No need to select of
course, try something like this

Sub test()
Dim rng As Range

On Error GoTo errH
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
End If
CleanUp:
' cleanup code

Exit Sub
errH:
Resume CleanUp
End Sub

or

On Error resume next ' anticipated error
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

 

[Trevor Williams]

Hi Peter - thanks for the response.

My procedure called CleanUp is the error handler, and basically protects the
activesheet and exits the sub - Is there a reason why the On Error line can't
go directly to CleanUp?

I'll make the amends as you've suggested...

Thanks again

Trevor

Your first method looks OK assuming code also includes an error handler, but
difficult to say based on the little you posted. No need to select of
course, try something like this

Sub test()
Dim rng As Range

On Error GoTo errH
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
End If
CleanUp:
' cleanup code

Exit Sub
errH:
Resume CleanUp
End Sub

or

On Error resume next ' anticipated error
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

Hi All

I'm using a bit of code to copy formulae into blank cells using
'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an error
on
the occassions when there are no blank cells. I've tried using an On
Error
statement but doesn't seem to work.

Any ideas how I get around the error msg?
Code below

Regards
Trevor Williams

Range("A1").CurrentRegion.Select
On Error GoTo CleanUp
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

also tried:

If Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select = True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

 

[Peter T]

Is there a reason why the On Error line can't go directly to CleanUp?

Should be fine also, like I said your first method looked OK.

Regards,
Peter T

Hi Peter - thanks for the response.

My procedure called CleanUp is the error handler, and basically protects
the
activesheet and exits the sub - Is there a reason why the On Error line
can't
go directly to CleanUp?

I'll make the amends as you've suggested...

Thanks again

Trevor

Your first method looks OK assuming code also includes an error handler,
but
difficult to say based on the little you posted. No need to select of
course, try something like this

Sub test()
Dim rng As Range

On Error GoTo errH
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
End If
CleanUp:
' cleanup code

Exit Sub
errH:
Resume CleanUp
End Sub

or

On Error resume next ' anticipated error
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

Hi All

I'm using a bit of code to copy formulae into blank cells using
'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an
error
on
the occassions when there are no blank cells. I've tried using an On
Error
statement but doesn't seem to work.

Any ideas how I get around the error msg?
Code below

Regards
Trevor Williams

Range("A1").CurrentRegion.Select
On Error GoTo CleanUp
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

also tried:

If Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select =
True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

 

[Trevor Williams]

OK, thanks again. I'll have a play.

Is there a reason why the On Error line can't go directly to CleanUp?

Should be fine also, like I said your first method looked OK.

Regards,
Peter T

Hi Peter - thanks for the response.

My procedure called CleanUp is the error handler, and basically protects
the
activesheet and exits the sub - Is there a reason why the On Error line
can't
go directly to CleanUp?

I'll make the amends as you've suggested...

Thanks again

Trevor

Your first method looks OK assuming code also includes an error handler,
but
difficult to say based on the little you posted. No need to select of
course, try something like this

Sub test()
Dim rng As Range

On Error GoTo errH
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
If Not rng Is Nothing Then
rng.FormulaR1C1 = "=R[-1]C"
End If
CleanUp:
' cleanup code

Exit Sub
errH:
Resume CleanUp
End Sub

or

On Error resume next ' anticipated error
Set rng = Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

message Hi All

I'm using a bit of code to copy formulae into blank cells using
'Selection.SpecialCells(xlCellTypeBlanks).Select'. I'm getting an
error
on
the occassions when there are no blank cells. I've tried using an On
Error
statement but doesn't seem to work.

Any ideas how I get around the error msg?
Code below

Regards
Trevor Williams

Range("A1").CurrentRegion.Select
On Error GoTo CleanUp
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

also tried:

If Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select =
True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If