deleting formulas and pasting values

[miconian]

Hi,

On page 114 of "Mr. Excel On Excel," this macro is put forth as the way to instantly convert cells containing formulas to cells containing the results of those formulas:

Selection.Formulas = Selection.Value

This doesn't work with the most common situation I would like to use it in: the sum of two cells. That is, if the formula is =SUM(B1+B2), running the macro returns "Run-time error '438': Object doesn't support this property or method."

What's up with that?

Thanks...

 

[starl]

what's your entire procedure? What's the selection?

 

[miconian]

Sub FormulaToValue()
'
' FormulaToValue Macro
' Erases a formula and writes the value of that formula to the same cell.
'
' Keyboard Shortcut: Ctrl+j
'
Selection.Formulas = Selection.Value
End Sub

I don't know what you mean by "What's the selection" beyond what I provided.

I just now tried it with the selection being cell A1, which contains:

=SUM(B1,B2)

and with cells B1 and B2 both containing the number 45, causing cell A1 to display 90.

 

[starl]

so it sounds like it worked this time? That line requires you to have the cell with the formula selected. What were you doing earlier?

 

[miconian]

No, it did not work this time.

I meant that cell A1 displays 90 because of the formula. But running that macro while having cell A1 selected does not remove the formula and paste the value. It gives the error mentioned earlier.

 

[starl]

oh! there's a TYPO.. it should be FORMULA (singular) not formulas (with an S)

 

[miconian]

Thanks. I'll let you know where to mail my share of the royalty checks. ; )

 

[starl]

sure, I'll give you a share of what I get..
nada
I didn't write this one!