Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
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 = ActiveSheet.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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
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" wrote in message ... 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 = ActiveSheet.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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 = ActiveSheet.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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
Hi Rick:
No, A(1) refers to the value of a single cell in a row (for example D3), A(2) for D4, etc. However, my selection is more than one column. Let me show you my selection. A B C D Index (Array B) 1 6 11 2 3 6 12 1 4 9 17 2 5 1 8 3 6 3 5 4 7 3 10 5 8 0 9 20 My original selection is C3:D7. A is an array of values of a column in my selection that things need to be calculated based on that column. Once I make the selection, and calculate my things, I obtain Array B, which is the order of how things should show. The results should be: A B C D Index (Array B) 1 6 11 2 3 9 17 2 4 3 5 4 5 1 8 3 6 3 10 5 7 6 12 1 8 0 9 20 I hope this clarifies things a bit. Bob "Rick Rothstein" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 = ActiveSheet.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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
That helps some, but you didn't answer all of my questions plus I now have a
couple of additional questions.. 1. Can the rows of data that you are rearranging be more than two cells wide? If so, what is the maximum number of columns? 2. Can there be other data on the rows (either in front of or after the data that you show being rearrange) that will not move when the designated columns are rearranged? 3. What is the maximum number of rows that will ever be included for rearrangement? 4. Do the Index numbers appear in your a column of your spreadsheet or do they only exist inside your code? -- Rick (MVP - Excel) "Bob" wrote in message ... Hi Rick: No, A(1) refers to the value of a single cell in a row (for example D3), A(2) for D4, etc. However, my selection is more than one column. Let me show you my selection. A B C D Index (Array B) 1 6 11 2 3 6 12 1 4 9 17 2 5 1 8 3 6 3 5 4 7 3 10 5 8 0 9 20 My original selection is C3:D7. A is an array of values of a column in my selection that things need to be calculated based on that column. Once I make the selection, and calculate my things, I obtain Array B, which is the order of how things should show. The results should be: A B C D Index (Array B) 1 6 11 2 3 9 17 2 4 3 5 4 5 1 8 3 6 3 10 5 7 6 12 1 8 0 9 20 I hope this clarifies things a bit. Bob "Rick Rothstein" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 = ActiveSheet.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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
Hi Bob,
Without code you would select the cells or cell, hold down the Shift key and drag the border of the cell to the desired location and release the mouse. Don't drag the fill handle, any other part of the border. Then do the same thing with the other cell, back to where the first one was. -- Thanks, Shane Devenshire "Bob" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Cell Formats
I have to do it with code.
"ShaneDevenshire" wrote in message ... Hi Bob, Without code you would select the cells or cell, hold down the Shift key and drag the border of the cell to the desired location and release the mouse. Don't drag the fill handle, any other part of the border. Then do the same thing with the other cell, back to where the first one was. -- Thanks, Shane Devenshire "Bob" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Error - Too many different cell formats. | Excel Discussion (Misc queries) | |||
error msg too many different cell formats how can I fix in excel | Excel Discussion (Misc queries) | |||
Excel cell formats | Excel Discussion (Misc queries) | |||
Formats: Too many different cell formats error message | Excel Programming | |||
How can I know the no of different cell formats in a Excel workbo. | Excel Discussion (Misc queries) |