I am trying to swap the contents and formats of two equally sized
ranges. (this is part of a specialised sorting routine).
The obvious way seems to be to use a DataObject to hold the formatted
text while doing the switch, but this appears to be a total failure.
Here is the code, try running it with two different words in cells A1
and B1

Sub test()
Dim A As New MSForms.DataObject
Sheet1.Range("A1").Cut
A.GetFromClipboard
Debug.Print "1 " & A.GetText
Sheet1.Range("A2").Copy Sheet1.Range("A1")
Debug.Print "2 " & A.GetText
B.PutInClipboard
Sheet1.Range("A2").PasteSpecial
End Sub

See how the Copy line alters the text property of A? Surely that isn't
right.
To compound matters, the PasteSpecial line crashes my Excel (2003) by
creating a blank text frame, and disabling all the menus....

Perhaps the DataObject is the wrong class to hold formatted range
data? Any suggestions how to swap two ranges while keeping the
formatting? Currently I am thinking of creating a temporary worksheet
and hiding it, but that seems very untidy.

Excel default is to use the formatting of the receiving range. To do what I
think you want to do, you would need to use a two step process. Step 1.
Range(x).Copy then Range(y).PasteSpecial Paste:=xlPasteFormats. Step 2.
Range(y).PasteSpecial Paste:=xlPasteValues

"atpgroups" wrote:

I am trying to swap the contents and formats of two equally sized
ranges. (this is part of a specialised sorting routine).
The obvious way seems to be to use a DataObject to hold the formatted
text while doing the switch, but this appears to be a total failure.
Here is the code, try running it with two different words in cells A1
and B1

Sub test()
Dim A As New MSForms.DataObject
Sheet1.Range("A1").Cut
A.GetFromClipboard
Debug.Print "1 " & A.GetText
Sheet1.Range("A2").Copy Sheet1.Range("A1")
Debug.Print "2 " & A.GetText
B.PutInClipboard
Sheet1.Range("A2").PasteSpecial
End Sub

See how the Copy line alters the text property of A? Surely that isn't
right.
To compound matters, the PasteSpecial line crashes my Excel (2003) by
creating a blank text frame, and disabling all the menus....

Perhaps the DataObject is the wrong class to hold formatted range
data? Any suggestions how to swap two ranges while keeping the
formatting? Currently I am thinking of creating a temporary worksheet
and hiding it, but that seems very untidy.

On 27 Dec, 17:33, JLGWhiz wrote:
Excel default is to use the formatting of the receiving range. *To do what I
think you want to do, you would need to use a two step process. Step 1.
Range(x).Copy then Range(y).PasteSpecial Paste:=xlPasteFormats. *Step 2.
Range(y).PasteSpecial Paste:=xlPasteValues

The copy and pasting isn't the real issue, the problem is how to
_swap_ the values and formats. ie How can I store the formats in range
X when pasting Range Y into it.

The DataObject's clipboard functions are limited to storing text
strings. Here's one way to swap the contents and formatting of two
cells, A1 and B1 in this case:

Dim R1 As Range
Dim R2 As Range
Dim Temp As Range
Dim WS As Worksheet
Set WS = ActiveSheet
Set R1 = Range("A1")
Set R2 = Range("B1")
With WS.UsedRange
Set Temp = .Item(.Cells.Count)(2, 1)
End With
R1.Copy Destination:=Temp
R2.Copy Destination:=R1
Temp.Copy Destination:=R2
Temp.Clear

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Sat, 27 Dec 2008 10:09:09 -0800 (PST), atpgroups
wrote:

On 27 Dec, 17:33, JLGWhiz wrote:
Excel default is to use the formatting of the receiving range. *To do what I
think you want to do, you would need to use a two step process. Step 1.
Range(x).Copy then Range(y).PasteSpecial Paste:=xlPasteFormats. *Step 2.
Range(y).PasteSpecial Paste:=xlPasteValues

The copy and pasting isn't the real issue, the problem is how to
_swap_ the values and formats. ie How can I store the formats in range
X when pasting Range Y into it.