PDA

View Full Version : Solved: Problem Rewriting a Short Macro



Cyberdude
10-07-2005, 11:32 AM
I have a short macro utility I call "ReverseCpyNCols" that is very useful to me, and I'd like to rewrite it to improve the efficiency a tad. The following shows the macro as I've been using it followed by the way I'd like to write it:
Sub ReverseCpyNCols1()
Selection.Copy
ActiveCell.Offset(-1, 0).Select
Selection.PasteSpecial Paste:=xlFormulas
Selection.Value = Selection.Value
End Sub

Sub ReverseCpyNCols2()
With Selection
.Copy .Offset(-1, 0)
.Offset(-1, 0).PasteSpecial Paste:=xlFormulas '<-This is invalid!
.Offset(-1, 0).Value = .Offset(-1, 0).Value
End With
End Sub
In general, what I want to know is...
Is there a way to copy just formulas or just formatting to a cell from another cell without using PasteSpecial?

mdmackillop
10-07-2005, 12:03 PM
How about

Sub ReverseCpyNCols2()
Selection.Offset(-1).FormulaR1C1 = Selection.FormulaR1C1
End Sub

mvidas
10-07-2005, 12:05 PM
Hi CD,

For the formulas, just use(to copy B1:B10's formulas into A1:A10) - keep in mind that unless the formulas have absolute references (ie $A$1) then the formula will shift.Range("A1:A10").Formula = Range("B1:B10").Formula

For the formats, you're pretty much stuck with paste special. If you wanted to avoid pastespecial for whatever reason, you could always do (to copy column B's formats into A):Columns("A").Insert
Columns("A").Value = Columns("B").Value
Columns("C").Copy Columns("B")
Columns("B").Value = Columns("A").Value
Columns("A").DeleteBut as I said, you're probably better off with paste special.

Matt

Cyberdude
10-07-2005, 12:16 PM
To Malcolm: I'll try the FormulaR1C1 approach to educate myself. (You like that one, don't you?)

To Matt: Thanks for the warning about the relative addresses problem. Hadn't thought of that. My main problem was how to get rid of PastSpecial because it requires a Select to anchor it. What I've been using works great, so I'll probably stick with it.

mdmackillop
10-07-2005, 01:10 PM
To Malcolm: I'll try the FormulaR1C1 approach to educate myself. (You like that one, don't you?)
I started with simple "formula" but it didn't work. R1C1 was my next trial and error option!

Bob Phillips
10-08-2005, 08:01 AM
Sub ReverseCpyNCols2()
With Selection
.Copy .Offset(-1, 0)
.Offset(-1, 0).PasteSpecial Paste:=xlFormulas '<-This is invalid!
.Offset(-1, 0).Value = .Offset(-1, 0).Value
End With
End Sub[/VBA]

This works fine for me, unless a cell in row 1 is selected.


Sub ReverseCpyNCols2()
With Selection
.Copy
.Offset(-1, 0).PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
End With
End Sub


In what way does it nor work for you?

.