Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Formula elements to values
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Formula elements to values
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 Student "Co-op Bank" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Formula elements to values
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 = "=+-*/^()" strForm = ActiveCell.Formula strOrig = ActiveCell.Formula For i = 1 To Len(Operators) strForm = Replace(strForm, Mid(Operators, i, 1), "*") Next i Addr = Split(strForm, "*") 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 "Co-op Bank" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Changing Formula elements to values
Excellent answer Bernie thankyou, this has saved alot of time.
Thanks Brian "Bernie Deitrick" wrote: 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 = "=+-*/^()" strForm = ActiveCell.Formula strOrig = ActiveCell.Formula For i = 1 To Len(Operators) strForm = Replace(strForm, Mid(Operators, i, 1), "*") Next i Addr = Split(strForm, "*") 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 "Co-op Bank" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
Keep formula from changing? | Excel Worksheet Functions | |||
Formula to compare multiple rows values based on another column? | Excel Worksheet Functions | |||
manipulating formula values | Excel Worksheet Functions |