Translate Formulas as Values

  • Thread starter Thread starter FF
  • Start date Start date
F

FF

Hello, is there any function that could directly translate a formula into its
numeric result, same as using the sequence:
- Ctrl+Ins
- Edit -> Paste Special
- Values


Thank you
 
no function can do that
functions cann only change cells' values
you would need a macro to do that

Sub transl()
For Each cell in Selection
cell.Copy
cell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Next cell
End Sub
 
I'm pretty sure this will do the same thing...

Sub transl()
For Each cell In Selection
cell.Value = cell.Value
Next
End Sub

Rick
 
Thanks, try to copy & past routine below into VB, but got an error msg
(sytnatx error) in correspondace to:
SkipBlanks _
:=False, Transpose:=False

Pls note that I've NO experience at all in VB macros, so -- myabe -- I'll
have to replace something in the VB routine you provide me with.
Thanks again for any hints
 
Your newsreader (mine too) broke the long line in an odd place. I have
restructured Jarek's code so that won't happen. Copy/paste this version into
your code window (or use the subroutine I posted) instead...

Sub transl()
For Each cell In Selection
cell.Copy
cell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Next cell
End Sub

Rick
 
Thanks Rick, your VB Macro doesn't return any error and could save the Module
by
assigning it a shortcut Ctrl+R... try go thru the worksheet to test it...
went to the cell with formula to be translated as value & press Ctr+R... the
result is that formula was deleted (blank value shown instead of its numeric
value)... for sure I'm doing something wrong... any help or instruction ?
Thank u so much
 
Thanks Rick, not sure if you got my last feedback, so I resend it.
The net is that now the Sub gets compiled correctly and no syntax error.
Hence I saved the Macro and assigned it to a shortcut key (Ctrl+R) and went
to test it on my worksheet...1) went thru to a cell with a formula inside; 2)
press Ctrl+R; 3) instead of seeing the cell formula replaced by its content,
the cell got emptied.... I assume something wrong on my side..... THANKS a lot
 
thks Rick, yours is simpler and quicker

;-)))

i've simplified mine too:

Sub transl()
For Each cell in Selection
cell.Copy
cell.PasteSpecial Paste:=xlPasteValues
Next cell
End Sub
 
Jarek

Sub transl()
For Each cell In Selection
cell.Value = cell.Value
Next cell
End Sub


Gord Dibben MS Excel MVP
 
But you can do it without a For/Next or a selection.

Dave Peterson showed me this construct.

Sub transl()
Dim rng As Range
Set rng = Nothing
On Error Resume Next 'just in case there are no formulas
Set rng = Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If rng Is Nothing Then
'do nothing
Else
rng.Value = rng.Value
End If
End Sub
 
Won't that pick up every cell with a formula, not just the ones in the
Selection (I'm thinking the OP might have used the Selection in order to
shield formulas elsewhere on the sheet)?

Rick
 
Certainly it will pick up every cell with a formula. It was designed to do just
that

If you re-read you will see that the code was presented to Jarek only as an
example of how to work without For/Next or a selection
 
Yes, I saw your qualification, but it (because it was short) left me with
the impression that you were suggesting it as a simple replacement for the
previously posted code. My thought was that the OP used the Selection to,
perhaps, shield other formulas from being processed and I just wanted a
comment for him to see indicating that your latest code would not do that.

Rick
 
Back
Top