• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cells.SpecialCells function help

frisbeenut

New Member
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.

Code:
'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

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

Code:
'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
 
Last edited:

Debaser

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

frisbeenut

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

Excel Ninja
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.

Code:
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
 
Top