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

D

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.
 
B

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
 
D

Dmitry Kopnichev

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

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.
 
Top