Testing for existence of a Range

P

Paul_B

Hi,

Bernie Deitrick gave this macro to me a few weeks ago for
converting relative to absolute references.

Sub ConvertFormulasToAbsoluteReferences()
Dim mycell As Range
For Each mycell In Selection.SpecialCells(xlCellTypeFormulas)
mycell.Formula = Application.ConvertFormula(mycell.Formula, xlA1,
xlA1, True)
Next mycell
End Sub

It works great, with the proviso that one must select a range
before activating it, or all cells in a sheet will be subject to
the macro.

Therefore, I'd like to add a test to the macro's front end. If no
range is preselected I'd like only the active cell to be
affected; if a range is preselected I'd like the entire range to
be affected, as is the case now.

Thanks for any help. Also hope to learn a bit more about ranges
by this exercise.

p.
 
D

Dave Peterson

How about:

Option Explicit
Sub ConvertFormulasToAbsoluteReferences()
Dim mycell As Range
Dim myRng As Range

Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))

If myRng Is Nothing Then
'do nothing
Else
For Each mycell In myRng.Cells
mycell.Formula _
= Application.ConvertFormula(mycell.Formula, xlA1, xlA1, True)
Next mycell
End If
End Sub
 
B

Bob Phillips

I don't get it, if only one cell is selected, that is the selection. Isn't
it?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
D

Dave Peterson

Not with the .specialcells stuff.

Bob said:
I don't get it, if only one cell is selected, that is the selection. Isn't
it?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...
 
J

JE McGimpsey

No, the domain for Selection.SpecialCells defaults to the entire
worksheet if the selection includes only one cell.

Try selecting a single cell in a populated worksheet and executing this
in the Immediate Window:

?selection.specialcells(xlconstants).address
 
P

Paul_B

That works great, Dave. Thanks very much. I'll also give it some
study so maybe I can pick up some principles.

p.
 
D

Dave Peterson

This is better -- it can avoid an error ...

Option Explicit
Sub ConvertFormulasToAbsoluteReferences()
Dim mycell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each mycell In myRng.Cells
mycell.Formula _
= Application.ConvertFormula(mycell.Formula, xlA1, xlA1, True)
Next mycell
End If
End Sub
 
P

Paul_B

Ok, will replace. Thanks again Dave.

p.


This is better -- it can avoid an error ...

Option Explicit
Sub ConvertFormulasToAbsoluteReferences()
Dim mycell As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0

If myRng Is Nothing Then
'do nothing
Else
For Each mycell In myRng.Cells
mycell.Formula _
= Application.ConvertFormula(mycell.Formula, xlA1, xlA1, True)
Next mycell
End If
End Sub
Click to expand...
 
Top