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

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.

I need to replace in 1000 cells automatically.
"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.

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


--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
I need to replace in 1000 cells automatically.
"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.

The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
need =b11+z11.
"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


--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
I need to replace in 1000 cells automatically.
"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.

That is not what you said originally.

Where does B11 + Z11 come from?

--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
need =b11+z11.
"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


--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
I need to replace in 1000 cells automatically.
"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.

The B11 is the cell from which the HLOOKUP(1...) returns value. The Z11 is
the cell from which the HLOOKUP(2...) returns value.
"Bob Phillips" ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ ×
ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: ...
That is not what you said originally.

Where does B11 + Z11 come from?

--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
need =b11+z11.
"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


--
HTH

Bob Phillips

"Dmitry Kopnichev" wrote in message
...
I need to replace in 1000 cells automatically.
"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.