Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Translate Formulas as Values
yep Gord, this one is the simpliest
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can someone please translate this for me | Excel Discussion (Misc queries) | |||
CELLS NOT CALC FORMULAS - VALUES STAY SME FORMULAS CORRECT?? HELP | Excel Worksheet Functions | |||
Why don't Excel formulas translate in copy/paste or fill down? | Excel Discussion (Misc queries) | |||
Please help translate the following | Excel Discussion (Misc queries) | |||
Do formulas in a table translate when sorted? | Excel Worksheet Functions |