formula to value

[IgorM]

In excel there when you edit a formula (press F2 in a cell with formula) and
than select whan of the arguments of that formula and press F9 you get value
instead of formula. For instance there is a formula "=A1+A2+A3", by
selecting A3 and pressing F9 you get "=A1+A2+25,36" where 25,36 is the value
from A3. Is there a way to do something like that in vba, that is to change
references to values in a formula?

 

[Rick Rothstein]

I think you will need to provide more information on how you want this to
work. Is the user selecting a reference or are you going to do that in code?
If in code, how does the code decide what reference to pick (or do you want
them all)? Where is the value(s) being returned to (that is, are you
changing the formula on the worksheet or just storing the value in a
variable)? Any other information you can give us on how you want the code to
interface with the formula in the cell would be helpful.

 

[Mike H.]

Sub doit()
Let x = ActiveCell.Value
ActiveCell.Value = x
End Sub