Cells.SpecialCells function help

[frisbeenut]

The code below will change all formulas in a sheet to relative row and absolute column style references.

I would like this function to work on only selected cells. Perhaps using an intersect function of two ranges.
The first range being the cells selected, and the second range being the range returned by .Cells.SpecialCells function.
Or maybe there is another simpler more direct way of doing this with VBA.

Thanks so much for any assistance.

'The code below will change all formulas in a sheet to relative row and absolute column style references.
'
'oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, 1)
'oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, xlAbsolute)
'xlAbsolute         1   Convert to absolute row and column style.
'xlAbsRowRelColumn  2   Convert to absolute row and relative column style.
'xlRelRowAbsColumn  3   Convert to relative row and absolute column style.
'xlRelative         4   Convert to relative row and column style.
'
Sub convertFormulasToAbsoluteCol()
    Dim oCell As Range
    With ActiveSheet
        For Each oCell In .Cells.SpecialCells(Type:=xlCellTypeFormulas)
        oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, 3)
    Next
End With

 

[frisbeenut]

I tried a different approach by not using Cells.SpecialCells function.
Seems to work for my needs.

'Requires parameter as follows

'xlAbsolute         1   Convert to absolute row and column style.
'xlAbsRowRelColumn  2   Convert to absolute row and relative column style.
'xlRelRowAbsColumn  3   Convert to relative row and absolute column style.
'xlRelative         4   Convert to relative row and column style.

Sub ConvertFormulasToAbsolute(ByVal i As Integer)
Dim oCell As Range
If i >= 1 And i <= 4 Then
   With ActiveSheet
     For Each oCell In Selection
     If oCell.HasFormula Then
        oCell.Formula = Application.ConvertFormula(oCell.Formula, xlA1, xlA1, i)
     End If
     Next
   End With
End If
End Sub

 

[Debaser]

You can simply use Selection.Specialcells but you should check first that the selection is more than one cell.

 

[frisbeenut]

Thanks. Since excel VBA does not have a built-in undo option, can someone update the macro to provide an undo function for this macro?

 

[Smallman]

Hi Frisbeenut

It is a bit quiet in my world so I decided to try editing your original code. The advantage is it will only focus on the cells with formula.

Sub ConvertFormulasToAbsolute() 'Add a reference for the cells in questions
Dim rng As Range
Dim i As Integer

i = InputBox("Add a number between 1 & 4", "Goski")

     For Each rng In Selection.SpecialCells(xlCellTypeFormulas)
        rng.Formula = Application.ConvertFormula(rng.Formula, xlA1, xlA1, i)
     Next

End Sub

Hope it helps you or someone else looking.

All the best

Smallman