help - Reference Locking an Array

[Mathias]

Hi Mr Excel,

Im faced with a mind numbing, finger crackingly tedious task and it would make my week if someone could save me with a little trick.

I need to copy/paste an array of cell's throughout a workbook. This array references a master array, such that if the data in the master array changes it should automatically change every other array.

So basically my secondary arrays contain "=A1"... and so on, referencing back to the master array. The only way I know how to copy the references without typing it over and over is to "F4" lock each cell in the secondary array and copy/paste it. But the process of "F4" is huge as I have like 500 cells to do.

Is there a trick please.
Thanks
Mathias

 

[RoryA]

You could select your first array of cells then run this code to convert all the references to absolute:

Sub MakeFormulasAbsolute()
   Dim rngcell As Range
   With Application
      .ScreenUpdating = False
      .Calculation = xlCalculationManual
   End With
   For Each rngcell In Selection
      If rngcell.HasFormula Then rngcell.Formula = _
            Application.ConvertFormula(rngcell.Formula, xlA1, xlA1, xlAbsolute)
   Next rngcell
   With Application
      .Calculation = xlCalculationAutomatic
      .ScreenUpdating = True
   End With
End Sub

though I don't really know why you want to repeat the same information throughout a workbook!