some more explanation

[IgorM]

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.

 

[Mike]

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

 

[IgorM]

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.

 

[Rick Rothstein]

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

 

[IgorM]

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

 

[Rick Rothstein]

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 Rothstein]

True, but I'm not sure that is how the OP wants to view the "evaluated"
formula. With Evaluate Formula, the evaluation is done step by step rather
than showing all the reference substitutions all at once. If I (or someone
else) can't come up with a method of bridging the sheets, that may end up
being the OP's only way to view the references.

 

[IgorM]

I realised that if I give you some background for the macro it might help
somehow.
I very often inspect many worksheets that have many long formulas (most of
them have reference to more than 10 worksheets). And because quite often
there are some errors (user typed in wrong numbers, etc) I've been using F9
to quickly ckeck a value that is behind each reference. But what I'm really
willing to do is to use watch window. So I'm aiming at building a macro that
would automatically extract all references from a cell formula and for each
of the extracted reference add a watch in a watch window.
Hope this helps.

 

[Rick Rothstein]

Okay, I developed the macro below based on my understanding of what I
thought you wanted originally and before I saw you latest posting. It shows
you the formula with all references replaced by their cell's content for the
active cell... it does this in a MessageBox so that you can view the results
in conjunction with the real formula in the Formula Bar. Is this acceptable?
If not, then please explain how you would want the "watch window" you
mentioned to work. Note that it would not be a dynamic display; rather, it
would be called from a macro like the code below is done.

Sub ShowCellValuesInFormula()
Dim R As Range
Dim X As Long
Dim LastRow As Long
Dim Frml As String
Dim Parts() As String
Dim WS As Worksheet
If ActiveCell.HasFormula And Not ActiveCell.HasArray Then
Frml = Replace(ActiveCell.Formula, "$", "")
On Error Resume Next
For Each WS In Worksheets
LastRow = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row + 1
With WS.Range("A" & LastRow)
.Formula = Frml
If Err.Number = 0 Then
For Each R In .Precedents
Parts = Split(Frml, R.Address(0, 0))
For X = 0 To UBound(Parts) - 1
If Parts(X) Like "*!" Then
If Parts(X) Like "*" & WS.Name & "!" Then
Frml = Replace(Frml, R.Address(0, 0),
WS.Range(R.Address).Text, 1, 1)
Frml = Replace(Frml, WS.Name & "!", "", 1, 1)
End If
Else
Frml = Replace(Frml, R.Address(0, 0), Range(R.Address).Text,
1, 1)
End If
Next
Next
.ClearContents
End If
End With
Next
End If
MsgBox Frml
End Sub

 

[IgorM]

Hi Rick
he
I run the macro below. The If Err.Number = 0 condition is only true once -
on the first run in a for loop. Then I get an error no 92 - For loop not
initialized. So the whole sub ends up showing me the message box with the
original formula from a cell, with references not values.
As for my recent background information. I though the Excel watch window
would be a good solution because it nicely shows the sheet name, formula of
a cell in that sheet and its value and when double clicked it quickly gets
me to specific cell. And yes I realise it would not be a dynamic display,
and I don't really want it to be dynamic, because I only want the macro to
be executed when a certain button or keyboard shortcut is pressed.
I appreciate your help very much.
Kind regards
Igor

 

[Rick Rothstein]

I'm not sure what you are referring to regarding the errors... when I try
the code out on my samples (using forced errors in multiple cells), the
macro still appears to work fine for me (it shows values where values exist
and the type of error, if any, at each cell where there is an error). Can
you show me the formula (and the values in the referenced cells) that
produces the problem you described so I can try to duplicate the problem
here? Once I figure out what is going on with respect to this, I'll look
into giving you a "watch window" version.

 

[IgorM]

Hi Rick

I've been away for couple of days. A sample formula: "='Corn maize
07'!D115+'Wheat+Barley 08'!D115+'Potatoes 08'!D115+'Potatoe Contractors
09'!D115+'Potatoes 2010'!D115+'Total Veg 09'!D115+'Crop Sum 09'!D115+'Crop
Sum 2010'!D115+'Foragecrop Total 09'!D115+'Foragecrop Total
2010'!D115+'Potatoes 09 GLUB'!D115+'Ware Onion 08'!D115-'Livestock
Total'!D121+'Pickle Onion 08'!D115" and their values
"=0+-10963,8710201116+-11825,9848615902+0+0+0+0+0+0+0+0+0-5708,16256364583+-780,433355334146"
(repleaced with F9 key). In fact all the referenced cells contain formulas,
which calculate a total of a particular worksheet.
I used the code provided by you, where I made only one change:
.Formula = Frml
If Err.Number <> 0 Then
mi = mi + 1
Debug.Print mi
Debug.Print Err.Number
Debug.Print Err.Description
End If
If Err.Number = 0 Then
For Each R In .Precedents

So when I run it with the cell that contains the abovementioned formula I
get the formula in the msgbox and in the Immediate window I get:
103
92
For loop not initialized

Hope it helps

Kind regards
Igor