Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Maintain Formulas of Copy and Cut

Status
Not open for further replies.

senators40

Technical User
Jan 7, 2003
68
CA
I would like to use the properties of cut for a formula while still maintaining the original place where the formula resided.

For instance if the formula is in cell A3 =Sum(A1:A2)

I want to move it to cell B3 and have the formula in B3 be =Sum(A1:A2) and still have cell A3 have =Sum(A1:A2)

I know you can use absolutes (with the $) to do it but sometimes the formulas are more complex

Manually I insert an apostrophe ' in front, then copy then paste to another cell and remove the apostrophe from both items. It works but I would like to know if there is a quicker way to do this through a macro.

Any help would be appreciated

Thanks,

Jeff
 




Did you try macro recording what you want to do?

Skip,
[sub]
[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue][/sub]
 
The easiest way without a macro is to Copy the formula text and paste it...
1. Double click the cell with the formula you want (eg A3)
2. Select the formula text
3. Copy it (Ctl+C, or right-mouse click and copy)
4. Paste it to your destination cell (B3)

But if you really want to use VBA then it's simply:
Range("B3").Formula = Range("A3").Formula

 
Did you know you can use F4 to switch between the absolute and relative references. To be honest, you are far better off doing this then trying to cut/copy and paste in this way - far less prone to error....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
For frequent use you can automate Spooku's second tip and create hotkey:
Code:
Sub CopyFormula()
Application.InputBox("Select target range", "Copy formula", , , , , , 8).Formula = Application.InputBox("Source range", "Copy formula", , , , , , 8).Formula
End Sub
I would rather try to redesign worksheet/calculations to avoid repeating the same formula.

combo
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top