Converting formulas to value - Advanced
Posted by Mary Beth on February 01, 2001 9:03 AM
I need to convert the cell references in a formula to its values. For instance, I have the formula
if(a1>b1, a1, b1)
a1=100
b1=80
I want to see in my cell: if(100>80,100,80) Obviously edit|past special|value is NOT what I want. Also, I need to do this using VBA because it will impact multiple spreadsheets (so the whole, highlight the cell reference and press F9 is not going to be the solution).
Alternatively, what if I need to subtract something?
if(a1>b1, a1-b1, b1)
I want to see: if(100>80, 100-80, 80)
THANK YOU!!
Posted by Carl B on February 03, 2001 7:22 PM
Not sure if this is what your looking for.
=If(Value(A1) > Value(B1), Value(A1), Value(B1))
=If(Value(A1) > Value(B1), Value(A1)-Value(B1)_
, Value(B1))
Posted by Mary Beth on February 04, 2001 4:21 PM
No, I need to remove any cell references and replace with the hard-coded values (similar operation as if you highlighted the formula and hit F9). If I look in the original cell, I only want to see numbers, no cell references. Any thoughts?
Posted by Carl B on February 05, 2001 10:15 PM
Re: not quite sure what your driving at yet, try this.
Posted by Mary Beth on February 06, 2001 4:01 PM
Re: not quite sure what your driving at yet, try this.
I want to remove any cell references in my formula because I will be deleting the source data and I need to hardcode the numbers. So, imagine that you have a formula that referenes cells A1 and B1 and you are going to delete A1 and B1. So, you need some way to "freeze" the values of A1 and B1 so that when the contents of these cells are deleted, you still have a valid value. Now, an edit|paste special|value would solve this problem EXCEPT that I want to be able to look at the formula and see the inputs. I want to see the calculated value in the cell B2, but if I edit the cell, I will see the If(100<200,0,200) formula.
Check out message ___. This solution works great for addition but it gives me errors when Im replacing values for subtraction or >,< operators.
Hope this helps to clariy.
Posted by Carl B on February 08, 2001 9:13 PM
Re: not quite sure what your driving at yet, try this.
I can't find any simple solution, try this..
'Row 1 (A1, B1, etc) have your values
'Row 2 have your formulas
'Row 3 have this formula ="If("&A1&">"&B1&","&A1&","&B1&")"
' to display the values and formula
'insert the macro below (changing the 6 to the number of columns you need this for)
'can also change it to Step 2 to skip every other row
'into VBA (alt+F11)
'To run the macro, from the Tools menu, select Macro, then select
' Macros (or Alt+F8), from the menu choose Sheet(#).ChangeToValues
Sub ChangeToValues()
For i = 1 To 6 Step 1
Cells(3, i).Select
Cells(3, i).Copy
Cells(4, i).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Next i
Range("B5").Select
End Sub
