|
Short cuts for converting formulas to values
Dear Sir,
May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK €¦. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 |
Short cuts for converting formulas to values
customize your menu bar to add the icon for paste values. (Right click menu bar, customize, commands, edit - and drag the paste values icon to your toolbar) Then just copy and click the icon. Of course it's always easier if you can have all the ones you want in one column. "Mr. Low" wrote: Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK €¦. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 |
Short cuts for converting formulas to values
this will do either for column A. Just comment out the one you don't want to
use. Sub fillinblanks() lr = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To lr If Cells(i + 1, "a") = "" Then Cells(i + 1, "a") = Cells(i, "a") Cells(i, "a").Value = Cells(i, "a").Value Next End Sub -- Don Guillett SalesAid Software "Mr. Low" wrote in message ... Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK .. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 |
Short cuts for converting formulas to values
#1. You can select the range
rightclick and grab it by the edge (not the autofill button) move it ever so slightly to the right (or left or top or bottom) then move it back and release the mouse button You'll see a popup. Choose Copy here as values only. #2. See Debra Dalgleish's site for some tips: http://contextures.com/xlDataEntry02.html Personally, I find that doing it manually is quicker than running the macro! Mr. Low wrote: Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK €¦. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 -- Dave Peterson |
Short cuts for converting formulas to values
Dear Dave,
I tried #1, it worked out well. Thanks Low -- A36B58K641 "Dave Peterson" wrote: #1. You can select the range rightclick and grab it by the edge (not the autofill button) move it ever so slightly to the right (or left or top or bottom) then move it back and release the mouse button You'll see a popup. Choose Copy here as values only. #2. See Debra Dalgleish's site for some tips: http://contextures.com/xlDataEntry02.html Personally, I find that doing it manually is quicker than running the macro! Mr. Low wrote: Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK €¦. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 -- Dave Peterson |
Short cuts for converting formulas to values
Dear Don,
Thanks for your info. Low -- A36B58K641 "Don Guillett" wrote: this will do either for column A. Just comment out the one you don't want to use. Sub fillinblanks() lr = Cells(Rows.Count, "a").End(xlUp).Row For i = 1 To lr If Cells(i + 1, "a") = "" Then Cells(i + 1, "a") = Cells(i, "a") Cells(i, "a").Value = Cells(i, "a").Value Next End Sub -- Don Guillett SalesAid Software "Mr. Low" wrote in message ... Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK .. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 |
Short cuts for converting formulas to values
Dear Widman,
Thanks for your help. It works out very well. Kind Regards Low -- A36B58K641 "widman" wrote: customize your menu bar to add the icon for paste values. (Right click menu bar, customize, commands, edit - and drag the paste values icon to your toolbar) Then just copy and click the icon. Of course it's always easier if you can have all the ones you want in one column. "Mr. Low" wrote: Dear Sir, May I know how to convert formulas to values under Excel 2002 and Excel 2007 ? Is Copy and Paste Special is the only way ? Any keyboard short cut or single button for this ? Is there any short cut way of filling up the blank cells with the reference just above it ? <Usually I copy and paste reference by reference Eg. Before AFD220 BLANK BLANK BLANK AFD221 BLANK BLANK AFD222 BLANK BLANK €¦. After AFD220 AFD220 AFD220 AFD220 AFD221 AFD221 AFD221 AFD222 AFD222 AFD222 Thanks Low -- A36B58K641 |
| All times are GMT +1. The time now is 04:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com