software

Hello, is there a way to change a formula for example '=a1 a2 a3' to show its
constant values instead in the cell so it would show '=10 20 30' assuming
'a1' = 10 etc...

Would this be a VBA solution? Any help suggestions would be much appreciated.

Regards
Brian
Manchester, England

Yes, it can be done in VBA:

1. Either code or down-load a simple table-driven parser for worksheet
constructs.
2. Run the parser on any cell to develop a table of cell references,
constants, named objects, etc.
3. Use the EVALUATE method on each item in the list to get the numeric value
4. Replace each item in the original expression with its equivalent numeric
value
5. Store the modified expression elsewhere.
--
Gary's Studentquot;Co-op Bankquot; wrote:

gt; Hello, is there a way to change a formula for example '=a1 a2 a3' to show its
gt; constant values instead in the cell so it would show '=10 20 30' assuming
gt; 'a1' = 10 etc...
gt;
gt; Would this be a VBA solution? Any help suggestions would be much appreciated.
gt;
gt; Regards
gt; Brian
gt; Manchester, England

Brian,

Select the cell with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.

HTH,
Bernie
MS Excel MVP

Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = quot;= -*/^()quot;

strForm = ActiveCell.Formula
strOrig = ActiveCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), quot;*quot;)
Next i

Addr = Split(strForm, quot;*quot;)
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

ActiveCell.Formula = strOrig
End Sub
quot;Co-op Bankquot; gt; wrote in message
...
gt; Hello, is there a way to change a formula for example '=a1 a2 a3' to show its
gt; constant values instead in the cell so it would show '=10 20 30' assuming
gt; 'a1' = 10 etc...
gt;
gt; Would this be a VBA solution? Any help suggestions would be much appreciated.
gt;
gt; Regards
gt; Brian
gt; Manchester, England
Excellent answer Bernie thankyou, this has saved alot of time.

Thanks
Brian

quot;Bernie Deitrickquot; wrote:

gt; Brian,
gt;
gt; Select the cell with the formula, and run the macro below. It will change most cell references to
gt; values, with the exception of multi-cell ranges. That would be do-able, but would require a
gt; re-write.
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt; Sub Convert()
gt; 'Converts cell references to values within the
gt; 'Activecell's formula
gt; 'Written by Bernie Deitrick Dec 15, 2005
gt;
gt; Dim strForm As String
gt; Dim strOrig As String
gt; Dim Addr As Variant
gt; Dim i As Integer
gt; Dim myCell As Range
gt; Const Operators As String = quot;= -*/^()quot;
gt;
gt; strForm = ActiveCell.Formula
gt; strOrig = ActiveCell.Formula
gt;
gt; For i = 1 To Len(Operators)
gt; strForm = Replace(strForm, Mid(Operators, i, 1), quot;*quot;)
gt; Next i
gt;
gt; Addr = Split(strForm, quot;*quot;)
gt; For i = LBound(Addr) To UBound(Addr)
gt; On Error GoTo NotCell
gt; Set myCell = Range(Addr(i))
gt; strOrig = Replace(strOrig, Addr(i), myCell.Value)
gt; NotCell:
gt; Resume GoOn
gt; GoOn:
gt; Next i
gt;
gt; ActiveCell.Formula = strOrig
gt; End Sub
gt;
gt;
gt;
gt;
gt;
gt;
gt; quot;Co-op Bankquot; gt; wrote in message
gt; ...
gt; gt; Hello, is there a way to change a formula for example '=a1 a2 a3' to show its
gt; gt; constant values instead in the cell so it would show '=10 20 30' assuming
gt; gt; 'a1' = 10 etc...
gt; gt;
gt; gt; Would this be a VBA solution? Any help suggestions would be much appreciated.
gt; gt;
gt; gt; Regards
gt; gt; Brian
gt; gt; Manchester, England
gt;
gt;
gt;