Re: Excel Cell Formats

We need some clarification on your *new* request (it is only remotely related to your original question). It looks like A(1) is referencing C3:D3, A(2) is referencing C4:D4, etc. Given that, what do you mean when you say A(1)=12, A(2)=17, etc.? I mean, how does a 2-cell range have a single value? Also, can I assume the A array references can be more than 2 cells wide? If so, what is the maximum width possible? What is the maximum number of rows that can be referenced by array A? Can I assume your ultimate request is to rearrange existing rows of data into a new ordering?

--
Rick (MVP - Excel)


"Bob" <someonw@xxxxxx> wrote in message news:e9XmIxbIJHA.4324@xxxxxxxxxxxxxxxxxxxxxxx
Thanks Rick for your sample code. However, I was wondering if you could help out more on this. You see, basically, I have two arrays. For example, array A contains the values of some continuous cells. For example For cells "C3:D7", A could be
A(1) = 12
A(2) = 17
A(3) = 8
A(4) = 5
A(5) = 10
Array B is the index of array A. So, initially, B=1,2,3,4,5. Then I do some work, and manipulate my arrays, and B becomes 2,4,3,5,1. Then I want to replace my original selected cells that gave us A, with A(B(i)), and that includes all formats. In other words,
New "C3:D3" (index 1) becomes Old "C4:D4 (index 2)
New "C4:D4" (index 2) becomes Old "C6:D6 (index 4)
New "C5:D5" (index 3) becomes Old "C5:D5 (index 3)
New "C6:D6" (index 4) becomes Old "C7:D7 (index 5)
New "C7:D7" (index 5) becomes Old "C3:D3 (index 1)

I tried to change your code to do this, but I was unsuccessful. Thanks for all your help.

Bob
"Rick Rothstein" <rick.newsNO.SPAM@xxxxxxxxxxxxxxxxxx> wrote in message news:eVKtjtaIJHA.4896@xxxxxxxxxxxxxxxxxxxxxxx
Select your two cells and give this macro a try...

Sub SwapCells()
Dim C1 As Range
Dim C2 As Range
Dim C3 As Range
If Selection.Count = 2 Then
Set C1 = Selection(1)
Set C2 = Range(Split(Replace(Selection.Address, ",", ":"), ":")(1))
Set C3 = Active***.Cells(Rows.Count, C1.Column).End(xlUp).Offset(1)
C1.Copy C3
C2.Copy C1
C3.Copy C2
C3.Clear
End If
End Sub

--
Rick (MVP - Excel)


"Bob" <someonw@xxxxxx> wrote in message news:%23bkNBFaIJHA.2208@xxxxxxxxxxxxxxxxxxxxxxx
Hi everyone:

I am trying to swap two cells completely, with each other, including value, All formats, colors, etc. Well, swapping the values is easy. Does anyone know, how I can swap All the formats (actual format, color, indent, etc) that a cell can hold? Thanks for your help.

Bob





.

Loading