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" <[email protected]> сообщил/сообщила в новостях следующее:
news:%[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> I need to replace in 1000 cells automatically.
> "Dmitry Kopnichev" <[email protected]> сообщил/сообщила в новостях
следующее:
> news:%[email protected]...
> > 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" <[email protected]> сообщил/сообщила в
новостях следующее: news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > I need to replace in 1000 cells automatically.
> > "Dmitry Kopnichev" <[email protected]> сообщил/сообщила в новостях
> следующее:
> > news:%[email protected]...
> > > 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" <[email protected]> wrote in message
news:[email protected]...
> The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
> need =b11+z11.
> "Bob Phillips" <[email protected]> сообщил/сообщила в
> новостях следующее: news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > I need to replace in 1000 cells automatically.
> > > "Dmitry Kopnichev" <[email protected]> сообщил/сообщила в новостях
> > следующее:
> > > news:%[email protected]...
> > > > 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" <[email protected]> сообщил/сообщила в
новостях следующее: news:OSQxWZ%[email protected]...
> That is not what you said originally.
>
> Where does B11 + Z11 come from?
>
> --
> HTH
>
> Bob Phillips
>
> "Dmitry Kopnichev" <[email protected]> wrote in message
> news:[email protected]...
> > The macro returns "Result or ==HLOOKUP(1...)+HLOOKUP(2...) = 365", but I
> > need =b11+z11.
> > "Bob Phillips" <[email protected]> сообщил/сообщила в
> > новостях следующее: news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I need to replace in 1000 cells automatically.
> > > > "Dmitry Kopnichev" <[email protected]> сообщил/сообщила в новостях
> > > следующее:
> > > > news:%[email protected]...
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>