XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select

  • Thread starter Trevor Williams
  • Start date
T

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
 
P

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
 
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


Peter T said:
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 said:
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
 
P

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

Trevor Williams said:
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


Peter T said:
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 said:
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
 
T

Trevor Williams

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

Peter T said:
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

Trevor Williams said:
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


Peter T said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top