Store formula for a while

[Alfredo_CPA]

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

 

[ker_01]

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

 

[Patrick Molloy]

you could use the sheet double click event for example to switch from
formula to value?

 

[Alfredo_CPA]

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