Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store formula for a while
This is not something will save a lot of time, but I'm just trying to be more
familiar with VBA. I'm wondering if there is a way to have a macro that stores a formula for a while (like an activate button) then another macro that paste the formula in selected cells and finally an "exit button" to stop storing that formula. I.e. I have a formula in c1 thatI will be using when analizing data. Once I'm in C500, I know I need to paste that formula and i don't want to scroll up to go and copy and then paste, instead I want to click a cutom buttom to paste that formula. (it is possible that i copy and paste other things before arriving to cell C500 so the "Application.CutCopyMode" will be false by then) The formula is perfectly relative to the new cell - then no problem with wrong references Any Ideas?? Thanks ws |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store formula for a while
I haven't done what you are describing, but I'll give you my thoughts (I'm
sure there are many ways to accomplish your request). If you just need to re-use one formula, I would write two macros, one that would take the formula in the current cell and write it to a custom document property, and another that would take the value of that custom document property and paste it in a selected cell. If you wanted to maintain more than one formula, you could create multiple custom document properties, but honestly I'd just confuse myself if I had to remember more than one new copy shortcut and one new paste shortcut. If I really needed more than one, I'd just create a new toolbar (yes, I'm still in 2003 :)) and set up pairs of custom buttons for each copy/paste custom property. You could even set the tooltip equal to the formula, so if you forgot which button was holding which formula, you could just mouseover. Another option would be to use VBA to proactively identify the lines that would need your target formula and paste the formula in each of those cells all at once... HTH, Keith "Alfredo_CPA" wrote: This is not something will save a lot of time, but I'm just trying to be more familiar with VBA. I'm wondering if there is a way to have a macro that stores a formula for a while (like an activate button) then another macro that paste the formula in selected cells and finally an "exit button" to stop storing that formula. I.e. I have a formula in c1 thatI will be using when analizing data. Once I'm in C500, I know I need to paste that formula and i don't want to scroll up to go and copy and then paste, instead I want to click a cutom buttom to paste that formula. (it is possible that i copy and paste other things before arriving to cell C500 so the "Application.CutCopyMode" will be false by then) The formula is perfectly relative to the new cell - then no problem with wrong references Any Ideas?? Thanks ws |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store formula for a while
you could use the sheet double click event for example to switch from
formula to value? "Alfredo_CPA" .(donotspam) wrote in message ... This is not something will save a lot of time, but I'm just trying to be more familiar with VBA. I'm wondering if there is a way to have a macro that stores a formula for a while (like an activate button) then another macro that paste the formula in selected cells and finally an "exit button" to stop storing that formula. I.e. I have a formula in c1 thatI will be using when analizing data. Once I'm in C500, I know I need to paste that formula and i don't want to scroll up to go and copy and then paste, instead I want to click a cutom buttom to paste that formula. (it is possible that i copy and paste other things before arriving to cell C500 so the "Application.CutCopyMode" will be false by then) The formula is perfectly relative to the new cell - then no problem with wrong references Any Ideas?? Thanks ws |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store formula for a while
How do I write the formula to a custom document property?
Thanks "ker_01" wrote: I haven't done what you are describing, but I'll give you my thoughts (I'm sure there are many ways to accomplish your request). If you just need to re-use one formula, I would write two macros, one that would take the formula in the current cell and write it to a custom document property, and another that would take the value of that custom document property and paste it in a selected cell. If you wanted to maintain more than one formula, you could create multiple custom document properties, but honestly I'd just confuse myself if I had to remember more than one new copy shortcut and one new paste shortcut. If I really needed more than one, I'd just create a new toolbar (yes, I'm still in 2003 :)) and set up pairs of custom buttons for each copy/paste custom property. You could even set the tooltip equal to the formula, so if you forgot which button was holding which formula, you could just mouseover. Another option would be to use VBA to proactively identify the lines that would need your target formula and paste the formula in each of those cells all at once... HTH, Keith "Alfredo_CPA" wrote: This is not something will save a lot of time, but I'm just trying to be more familiar with VBA. I'm wondering if there is a way to have a macro that stores a formula for a while (like an activate button) then another macro that paste the formula in selected cells and finally an "exit button" to stop storing that formula. I.e. I have a formula in c1 thatI will be using when analizing data. Once I'm in C500, I know I need to paste that formula and i don't want to scroll up to go and copy and then paste, instead I want to click a cutom buttom to paste that formula. (it is possible that i copy and paste other things before arriving to cell C500 so the "Application.CutCopyMode" will be false by then) The formula is perfectly relative to the new cell - then no problem with wrong references Any Ideas?? Thanks ws |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Store formula for a while
I became with this two codes. My only question now is; is it possible to copy to/from a files without open it? (my macro open and closes the file): Sub StoreFormula() Application.ScreenUpdating = False MyFormulaCell = ActiveCell.Address Selection.Copy Workbooks.Open Filename:="C:\Documents and Settings\aramos\My Documents\MyFormulaFile.xls" Range(MyFormulaCell).Select ActiveSheet.Paste ActiveWorkbook.Close SaveChanges:=True Application.CutCopyMode = False End Sub Sub UseStoredFormula() MyFile = ActiveWorkbook.Name Application.ScreenUpdating = False Workbooks.Open Filename:="C:\Documents and Settings\aramos\My Documents\MyFormulaFile.xls" Selection.Copy Windows(MyFile).Activate ActiveSheet.Paste Windows("MyFormulaFile.xls").Activate Application.CutCopyMode = False ActiveWorkbook.Close SaveChanges:=False End Sub "Alfredo_CPA" wrote: How do I write the formula to a custom document property? Thanks "ker_01" wrote: I haven't done what you are describing, but I'll give you my thoughts (I'm sure there are many ways to accomplish your request). If you just need to re-use one formula, I would write two macros, one that would take the formula in the current cell and write it to a custom document property, and another that would take the value of that custom document property and paste it in a selected cell. If you wanted to maintain more than one formula, you could create multiple custom document properties, but honestly I'd just confuse myself if I had to remember more than one new copy shortcut and one new paste shortcut. If I really needed more than one, I'd just create a new toolbar (yes, I'm still in 2003 :)) and set up pairs of custom buttons for each copy/paste custom property. You could even set the tooltip equal to the formula, so if you forgot which button was holding which formula, you could just mouseover. Another option would be to use VBA to proactively identify the lines that would need your target formula and paste the formula in each of those cells all at once... HTH, Keith "Alfredo_CPA" wrote: This is not something will save a lot of time, but I'm just trying to be more familiar with VBA. I'm wondering if there is a way to have a macro that stores a formula for a while (like an activate button) then another macro that paste the formula in selected cells and finally an "exit button" to stop storing that formula. I.e. I have a formula in c1 thatI will be using when analizing data. Once I'm in C500, I know I need to paste that formula and i don't want to scroll up to go and copy and then paste, instead I want to click a cutom buttom to paste that formula. (it is possible that i copy and paste other things before arriving to cell C500 so the "Application.CutCopyMode" will be false by then) The formula is perfectly relative to the new cell - then no problem with wrong references Any Ideas?? Thanks ws |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To store the formula in a string | Excel Discussion (Misc queries) | |||
is it possible to store formula under a UDF | Excel Programming | |||
store inventory sheet(ex:sports equipment store) | Excel Worksheet Functions | |||
Store formula in Cell | Excel Discussion (Misc queries) | |||
Store formula in Cell | Excel Discussion (Misc queries) |