Translate Formulas as Values
 

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

Translate Formulas as Values
 

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

Translate Formulas as Values
 

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


"Jarek Kujawa" wrote in message
...
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

Translate Formulas as Values
 

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

"Jarek Kujawa" wrote:

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

Translate Formulas as Values
 

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


"FF" wrote in message
...
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

"Jarek Kujawa" wrote:

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

Translate Formulas as Values
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"Jarek Kujawa" wrote in message
...
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

Translate Formulas as Values
 

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

"Rick Rothstein (MVP - VB)" wrote:

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


"FF" wrote in message
...
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

"Jarek Kujawa" wrote:

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

Translate Formulas as Values
 

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

Translate Formulas as Values
 

Jarek

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


Gord Dibben MS Excel MVP

On Wed, 25 Jun 2008 00:28:59 -0700 (PDT), Jarek Kujawa wrote:

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

Translate Formulas as Values
 

yep Gord, this one is the simpliest

Translate Formulas as Values
 

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



On Wed, 25 Jun 2008 23:05:42 -0700 (PDT), Jarek Kujawa wrote:

yep Gord, this one is the simpliest

Translate Formulas as Values
 

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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



On Wed, 25 Jun 2008 23:05:42 -0700 (PDT), Jarek Kujawa
wrote:

yep Gord, this one is the simpliest

Translate Formulas as Values
 

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

But you can do it without a For/Next or a selection.


Gord

On Sun, 29 Jun 2008 10:56:47 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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



On Wed, 25 Jun 2008 23:05:42 -0700 (PDT), Jarek Kujawa
wrote:

yep Gord, this one is the simpliest

Translate Formulas as Values
 

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

But you can do it without a For/Next or a selection.


Gord

On Sun, 29 Jun 2008 10:56:47 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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


"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
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



On Wed, 25 Jun 2008 23:05:42 -0700 (PDT), Jarek Kujawa

wrote:

yep Gord, this one is the simpliest