lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by pressing
a shortcut or some button) runs a macro, that macro will produce in cell B1
the following formula "=5,5+4,0+5,4", so in other words it will change the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way Mike H.
proposes. I want to let the user see all values not having to search for it
in a spreadsheet.

=A1&" " &A2&" " &A3

"IgorM" wrote:

lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by pressing
a shortcut or some button) runs a macro, that macro will produce in cell B1
the following formula "=5,5+4,0+5,4", so in other words it will change the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way Mike H.
proposes. I want to let the user see all values not having to search for it
in a spreadsheet.

It is not what i ment. I don't want to change it to string. I just want to
replace raferences with the values stored in the referenced cell and I want
to keep any math signs as well (+, -, /, etc.). So I want to write a macro
that will do the same thing as entering a cell and changing each reference
to a value like when using F9 but keeping math signs like +, -, / , etc. at
the same time.
"Mike" wrote in message
...
=A1&" " &A2&" " &A3

"IgorM" wrote:

lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by
pressing
a shortcut or some button) runs a macro, that macro will produce in cell
B1
the following formula "=5,5+4,0+5,4", so in other words it will change
the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way Mike
H.
proposes. I want to let the user see all values not having to search for
it
in a spreadsheet.

Give this macro a try (it shows you the formula for the active cell with
references replaced by values in a MessageBox provided the cell has a
formula that is not part of an array formula)...

Sub ShowCellValuesInFormula()
Dim R As Range
Dim Frml As String
With ActiveCell
If .HasFormula And Not .HasArray Then
Frml = Replace(.Formula, "$", "")
For Each R In .Precedents
Frml = Replace(Frml, R.Address(0, 0), Range(R.Address).Value)
Next
End If
End With
MsgBox Frml
End Sub

--
Rick (MVP - Excel)


"IgorM" wrote in message
...
It is not what i ment. I don't want to change it to string. I just want to
replace raferences with the values stored in the referenced cell and I
want to keep any math signs as well (+, -, /, etc.). So I want to write a
macro that will do the same thing as entering a cell and changing each
reference to a value like when using F9 but keeping math signs like +, -,
/ , etc. at the same time.
"Mike" wrote in message
...
=A1&" " &A2&" " &A3

"IgorM" wrote:

lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by
pressing
a shortcut or some button) runs a macro, that macro will produce in cell
B1
the following formula "=5,5+4,0+5,4", so in other words it will change
the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way
Mike H.
proposes. I want to let the user see all values not having to search for
it
in a spreadsheet.

Hi Rick

Thanks for the macro below. It's great. But what if the reference is to
(precedent is in) another sheet. I get a run-time error '1004'. No cells
were found.

Kind regards

Igor
U¿ytkownik "Rick Rothstein" napisa³ w
wiadomo¶ci ...
Give this macro a try (it shows you the formula for the active cell with
references replaced by values in a MessageBox provided the cell has a
formula that is not part of an array formula)...

Sub ShowCellValuesInFormula()
Dim R As Range
Dim Frml As String
With ActiveCell
If .HasFormula And Not .HasArray Then
Frml = Replace(.Formula, "$", "")
For Each R In .Precedents
Frml = Replace(Frml, R.Address(0, 0), Range(R.Address).Value)
Next
End If
End With
MsgBox Frml
End Sub

--
Rick (MVP - Excel)


"IgorM" wrote in message
...
It is not what i ment. I don't want to change it to string. I just want
to replace raferences with the values stored in the referenced cell and I
want to keep any math signs as well (+, -, /, etc.). So I want to write a
macro that will do the same thing as entering a cell and changing each
reference to a value like when using F9 but keeping math signs like
+, -, / , etc. at the same time.
"Mike" wrote in message
...
=A1&" " &A2&" " &A3

"IgorM" wrote:

lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by
pressing
a shortcut or some button) runs a macro, that macro will produce in
cell B1
the following formula "=5,5+4,0+5,4", so in other words it will change
the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way
Mike H.
proposes. I want to let the user see all values not having to search
for it
in a spreadsheet.

Handling references to other sheets might be more problematic... the
Precedents method only identifies references on the active sheet (I'm not
aware of a method that works across sheets at the moment). Give me a little
time to see if I can work around the problem or not. I have a couple of
ideas that may work, but I am not sure about them yet... check back later in
the day (it is 9:30am here right now) and see if I came up with a solution
or not. The error you got is because I forgot to build in an trap when there
are no (Precedents) references found in the formula.

--
Rick (MVP - Excel)


"IgorM" wrote in message
.. .
Hi Rick

Thanks for the macro below. It's great. But what if the reference is to
(precedent is in) another sheet. I get a run-time error '1004'. No cells
were found.

Kind regards

Igor
U¿ytkownik "Rick Rothstein" napisa³
w wiadomo¶ci ...
Give this macro a try (it shows you the formula for the active cell with
references replaced by values in a MessageBox provided the cell has a
formula that is not part of an array formula)...

Sub ShowCellValuesInFormula()
Dim R As Range
Dim Frml As String
With ActiveCell
If .HasFormula And Not .HasArray Then
Frml = Replace(.Formula, "$", "")
For Each R In .Precedents
Frml = Replace(Frml, R.Address(0, 0), Range(R.Address).Value)
Next
End If
End With
MsgBox Frml
End Sub

--
Rick (MVP - Excel)


"IgorM" wrote in message
...
It is not what i ment. I don't want to change it to string. I just want
to replace raferences with the values stored in the referenced cell and
I want to keep any math signs as well (+, -, /, etc.). So I want to
write a macro that will do the same thing as entering a cell and
changing each reference to a value like when using F9 but keeping math
signs like +, -, / , etc. at the same time.
"Mike" wrote in message
...
=A1&" " &A2&" " &A3

"IgorM" wrote:

lets assume the following:
cell A1 value 5,5
cell A2 value 4,0
cell A3 value 5,4

in, say, cell B1 there is a formula "=A1+A2+A3". When the user (by
pressing
a shortcut or some button) runs a macro, that macro will produce in
cell B1
the following formula "=5,5+4,0+5,4", so in other words it will change
the
referneces in the selected cell (if of course there are any) to values
stored in the referenced cell. I don't want to sum them all in a way
Mike H.
proposes. I want to let the user see all values not having to search
for it
in a spreadsheet.