Forum Discussion

MelvynM's avatar
MelvynM
Copper Contributor
Feb 06, 2024

Excel - Evaluate two times a formula

Hello,

In order to do statistics I made this formula (#1):

=(SUBSTITUE(SUBSTITUE("=SUBSTITUE(GAUCHE(CELLULECHOISIE;TROUVE(¶;¶;CELLULECHOISIE)-1);¶[¶;¶¶)";"¶";"""");"CELLULECHOISIE";SUBSTITUE("VALEUR.EN.TEXTE(C20)";"C20";CELLULE("adresse";C21))))

 

Basically, this formula creates a new formula based on the cell selected (C21 in this case) resulting to: 

=SUBSTITUE(GAUCHE(VALEUR.EN.TEXTE($C$21);TROUVE(";";VALEUR.EN.TEXTE($C$21))-1);"[";"")

 

So, if in a cell (A1) I enter the formula #1, and select the cell C21, the visible result in A1 will be formula 2. This second formula, once copy pasted on an other cell gives a result depending of what C21 contains. The probleme is: I'd like to make it so that the result from formula 2 appears in the same cell I put formula 1 (A1 in this case).

 

In other words, I need 1 fonction that evaluates itself two times. Exemple: this function:

="=1+1" 

Gives as result a new formula:

=1+1

And this formula gives a new result 2. These two steps need to be in the same cell. 

Any idea how to do that, with VBA maybe?

 

Thank you,

 

Mel

 

excel
Formulas and Functions
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 08, 2024

    MelvynM 

    To achieve the functionality you described, where a formula in a cell evaluates to another formula and then to its result, all within the same cell, you can use a VBA (Visual Basic for Applications) macro.

    Here is a possible VBA approach/proposal:

    Sub EvaluateFormula()
    Dim rng As Range
    Dim formula1 As String
    Dim formula2 As String
    Dim result As Variant

    ' Assuming the formula is in cell A1
    Set rng = Range("A1")
    
    ' Get the formula from cell A1
    formula1 = rng.Formula
    
    ' Evaluate the formula to get formula2
    formula2 = Application.Evaluate(formula1)
    
    ' Evaluate formula2 to get the result
    result = Application.Evaluate(formula2)
    
    ' Write the result back to the same cell (A1)
    rng.Value = result
End Sub

    Make sure you adjust the cell reference in the code if your formula is located in a different cell. The text was created with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources