Replace part of a formula with its calculated value

[howerd]

Hi - I am looking to replace part of a formula with its calculated value for a number of different cells.

I know how to do this manually, however I have a number of cells, so I wondered if there is a quicker way to replace multiple cells?

The formula includes rand() which is generating a number between 0 and 1, however I don't want it to re-calculate every time.

As an example all of the cells have part of the formula as following:

NORMINV(RAND(),$B$3,$B$4)

and I want the calculated value for rand() to be showing in each cell instead, like this:

NORMINV(0.1645148,$B$3,$B$4)

Any help on how to do this over multiple cells would be gratefully received.

Thanks

 

[RoryA]

You'd have to recalculate a new Rand() value at each replacement - would that be acceptable?

 

[howerd]

Hi RoryA,

Thanks for getting back to me.

Yes, so I am looking for each cell to have a different random number in.

Thanks

 

[RoryA]

You could do something like this then:

Sub UpdateRandFormulas()
   Dim formulaRange As Range
   On Error Resume Next
   Set formulaRange = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
   On Error GoTo 0
   
   If Not formulaRange Is Nothing Then
      Application.Calculation = xlCalculationManual
      Dim cell As Range
      For Each cell In formulaRange.Cells
         If InStr(1, cell.Formula, "RAND()") <> 0 Then
            cell.Formula = Replace$(cell.Formula, "RAND()", Rnd)
         End If
      Next cell
      Application.Calculation = xlCalculationAutomatic
   End If
End Sub

 

[howerd]

That has worked perfectly, thank you so much RoryA!