XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
 

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(xlCellTypeB lanks).Select = True Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
 

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(xlCellTypeB lanks)
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(xlCellTypeB lanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

"Trevor Williams" wrote in
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(xlCellTypeB lanks).Select = True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
 

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" wrote:

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(xlCellTypeB lanks)
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(xlCellTypeB lanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

"Trevor Williams" wrote in
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(xlCellTypeB lanks).Select = True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
 

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

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(xlCellTypeB lanks)
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(xlCellTypeB lanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

"Trevor Williams" wrote in
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(xlCellTypeB lanks).Select =
True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If

XL2002: Selection.SpecialCells(xlCellTypeBlanks).Select
 

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

"Peter T" wrote:

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

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(xlCellTypeB lanks)
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(xlCellTypeB lanks)
On Error GoTo errH ' resume normal error handling
if not rng is nothing then

Regards,
Peter T

"Trevor Williams" wrote in
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(xlCellTypeB lanks).Select =
True
Then
Selection.FormulaR1C1 = "=R[-1]C"
End If