Testing for existence of a Range

  • Thread starter Thread starter Paul_B
  • Start date Start date
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.
 
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
 
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)
 
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...
 
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
 
That works great, Dave. Thanks very much. I'll also give it some
study so maybe I can pick up some principles.

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
 
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...
 
Back
Top