How to replace a function with its result or resulting reference in a formula?

[Dmitry Kopnichev]

Hello
How to replace a function with its result in a formula? For example,
=INDEX(...)+INDEX(...) with
=result_of_INDEX(a...)+result_of_INDEX(b...)=1025+1307.
How to replace a function with its resulting reference in a formula? For
example, =HLOOKUP(1...)+HLOOKUP(2...) with =b11+z11.

 

[Dmitry Kopnichev]

I need to replace in 1000 cells automatically.

 

[Bob Phillips]

A little macro

Sub ReplaceFormulae()
Dim cell As Range
Application.Calculation = xlCalculationManual
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
cell.Value = "Result of " & cell.Formula & _
" = " & cell.text
End If
Next cell
Application.Calculation = xlCalculationAutomatic
End Sub

 

[Dmitry Kopnichev]

The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
need =b11+z11.

 

[Bob Phillips]

That is not what you said originally.

Where does B11 + Z11 come from?

 

[Dmitry Kopnichev]

The B11 is the cell from which the HLOOKUP(1...) returns value. The Z11 is
the cell from which the HLOOKUP(2...) returns value.