Formula replaced by its value?

  • Thread starter Thread starter Jean Fontaine
  • Start date Start date
J

Jean Fontaine

In an Excel spreadsheet involving many iterations, I'm trying to get rid of
circular formulas. It would be useful if I could write a formula this way:
as long as the result of the formula is not a number, the cell should keep
being a formula, but once the result of the formula is a number, then the
cell should be at once converted into that number and not be a formula
anymore. The formula should be replaced by its numerical value.

Is there a way to do that?

Thanks,

JF
 
I think that something like this would scare me a lot!

But if you define a range on that worksheet and give it a nice name, then you
could cycle through those cells whenever the worksheet recalculated.

Select your cells and then:
Insert|Name|define
I used: myCellsToCheck

Then rightclick on the worksheet tab, select view code and paste this in:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myCellsToCheck As Range
Dim myCell As Range

Set myCellsToCheck = Me.Range("mycellstocheck")
For Each myCell In myCellsToCheck.Cells
With myCell
If .HasFormula Then
If IsNumeric(.Value) Then
.Value = .Value
End If
End If
End With
Next myCell

End Sub
 
Back
Top