Change a range to absolute (2003 SP3)

[steveh]

Morning all

I have a range of data M45:W149 which all apart from Column M which has a normal = formula have array formulas.

I would like to make all of them absolute, so that I can sort it (which is not possible at source). If I select the whole range and use the ASAP formula function it changes them all as required but all except the top row M45:W45 show as ### which means going into each individual cell to reconfirm the array with Ctrl-Shift-Enter.

Is there a way to select the whole of the affected range and perform the Ctrl-Shift-Enter at the same time?

[HansV]

What exactly do you mean by "make all of them absolute"?

[steveh]

Hi Hans

Sorry, I probably used the wrong terminology, I thought that using the $ made a cell reference absolute.

I have loads of cells (N45:W149) all containing array formulas, column M has an ordinary = Formula. If I try to put the whole range in alphabetical order it changes the cell references and everything gets an error. I thought therefore that by using the $ it would always point to that cell no matter where it was moved to. For example =AVERAGE(VALUE(SUBSTITUTE(B61:B62,"°",""))) Would become =AVERAGE(VALUE(SUBSTITUTE($B6$1:$B$62,"°",""))).

The ASAP utility has a function that will change them all for me into the preferred format but in doing so loses the Ctrl-Shift-Enter that I had originally done in each cell so what I was trying to achieve was somehow accepting the all of the arrays at the same time rather than going back into each cell and using Ctrl-Shift-Enter

[HansV]

That's what I thought, but I wanted to make sure (you don't want the range to be made absolute, but the cell references in the formulas).

Try this macro (based on one by Andy Pope):

Code: Select all

Sub Rel2Abs()
  Dim oCell As Range
  Dim strFormula As String
  For Each oCell In Selection.SpecialCells(xlCellTypeFormulas)
    If oCell.HasArray Then
      strFormula = oCell.FormulaArray
      strFormula = Application.ConvertFormula(strFormula, xlA1, xlR1C1, xlAbsolute)
      oCell.CurrentArray.FormulaArray = strFormula
    Else
      strFormula = oCell.Formula
      strFormula = Application.ConvertFormula(strFormula, xlA1, xlA1, xlAbsolute)
      oCell.Formula = strFormula
    End If
  Next oCell
End Sub

[steveh]

Hi Hans

Absolute(ly) terrific

Thanks once again