If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro??
Hi, to explain what I am trying to achieve is this.
I have several columns and rows of data and I wish to move certain cells around in the same row. I have made a macro that moves 2 cells to a blank space, move 2 other cells into its cells and the moves the first 2 cells in its place however the macro alway returns to the cell address where I made the macro I would like it to commit what ever row i am in Hope u can understand what I am trying to achieve. 1 ONEMORENOMORE 3 DELAGO BOLT 3 GRAHAM 8 WILLIAM The macro in this case would swap n08 william with 3 Graham Regards Graham |
#2
|
|||
|
|||
Macro??
Here you go:
Sub SwapCellPairs() 'assumes you have selected the 'cell at the far left of a group 'to be swapped, and that the '"distance" between cells to be 'swapped is always the same ' 'how many columns from left-most of 'first group to first one in 2nd group? 'Example first group is in columns A and B 'second group is in E and F 'it is 4 columns from A to E Const firstGap = 4 Dim firstValue As Variant ' temporary holding Dim secondValue As Variant ' temporary holding 'save the first group's values firstValue = ActiveCell.Value secondValue = ActiveCell.Offset(0, 1).Value 'move second group into 1st group's cells ActiveCell.Value = _ ActiveCell.Offset(0, firstGap).Value ActiveCell.Offset(0, 1).Value = _ ActiveCell.Offset(0, firstGap + 1).Value 'move the 1st group over to where the '2nd group is ActiveCell.Offset(0, firstGap).Value = _ firstValue ActiveCell.Offset(0, firstGap + 1).Value = _ secondValue 'all done! End Sub To put the code in your workbook, open it up and press [Alt]+[F11] to open the vB Editor. In the VBE choose Insert -- Module and copy and paste the code above into that. Make any change you need to to the Constant value named firstGap. Close the VBE. Back in the worksheet view, choose Tools -- Macro -- Macros and highlight the name of the macro and use the [Options...] button to assign it a shortcut so that you can use it quickly. After that, all you have to do is select the left-most cell on a row wher you want to swap things and hit your shortcut key combination to swap the two groups. If you accidentally swap a wrong pair, just do it again to put them back the way they were. Enjoy! "Graham Feeley" wrote: Hi, to explain what I am trying to achieve is this. I have several columns and rows of data and I wish to move certain cells around in the same row. I have made a macro that moves 2 cells to a blank space, move 2 other cells into its cells and the moves the first 2 cells in its place however the macro alway returns to the cell address where I made the macro I would like it to commit what ever row i am in Hope u can understand what I am trying to achieve. 1 ONEMORENOMORE 3 DELAGO BOLT 3 GRAHAM 8 WILLIAM The macro in this case would swap n08 william with 3 Graham Regards Graham |
#3
|
|||
|
|||
Macro??
Ok first of all I thank you for your response and promptly I may say.
Ok I explained it all wrong, I think u mean to move columns. It is rows I am working in and in fact it will be always the same I have columns A through to M I wish to move K16:L16 to the right 4 columns (or saved to variants) then move C16:C16 to K16:L16 then move K16:L16 to C16:C16 in other words it swaps 2 cells in the same row I thank you for your help and I have save your code for futher reading Regards Graham "JLatham" wrote in message ... Here you go: Sub SwapCellPairs() 'assumes you have selected the 'cell at the far left of a group 'to be swapped, and that the '"distance" between cells to be 'swapped is always the same ' 'how many columns from left-most of 'first group to first one in 2nd group? 'Example first group is in columns A and B 'second group is in E and F 'it is 4 columns from A to E Const firstGap = 4 Dim firstValue As Variant ' temporary holding Dim secondValue As Variant ' temporary holding 'save the first group's values firstValue = ActiveCell.Value secondValue = ActiveCell.Offset(0, 1).Value 'move second group into 1st group's cells ActiveCell.Value = _ ActiveCell.Offset(0, firstGap).Value ActiveCell.Offset(0, 1).Value = _ ActiveCell.Offset(0, firstGap + 1).Value 'move the 1st group over to where the '2nd group is ActiveCell.Offset(0, firstGap).Value = _ firstValue ActiveCell.Offset(0, firstGap + 1).Value = _ secondValue 'all done! End Sub To put the code in your workbook, open it up and press [Alt]+[F11] to open the vB Editor. In the VBE choose Insert -- Module and copy and paste the code above into that. Make any change you need to to the Constant value named firstGap. Close the VBE. Back in the worksheet view, choose Tools -- Macro -- Macros and highlight the name of the macro and use the [Options...] button to assign it a shortcut so that you can use it quickly. After that, all you have to do is select the left-most cell on a row wher you want to swap things and hit your shortcut key combination to swap the two groups. If you accidentally swap a wrong pair, just do it again to put them back the way they were. Enjoy! "Graham Feeley" wrote: Hi, to explain what I am trying to achieve is this. I have several columns and rows of data and I wish to move certain cells around in the same row. I have made a macro that moves 2 cells to a blank space, move 2 other cells into its cells and the moves the first 2 cells in its place however the macro alway returns to the cell address where I made the macro I would like it to commit what ever row i am in Hope u can understand what I am trying to achieve. 1 ONEMORENOMORE 3 DELAGO BOLT 3 GRAHAM 8 WILLIAM The macro in this case would swap n08 william with 3 Graham Regards Graham |
#5
|
|||
|
|||
Macro??
[System hiccuped - not sure the post went, posting again]...
Actually that's what the code does. If you changed groupGap to = 8 and then click the cell in column C of the row you wanted to swap pairs with, it would do that. One question: you wrote K16:L16 which indicates a pair of cells, but you wrote C16:C16 twice, I think (have assumed) that you meant for that to be C1616. Anyhow, give that you want to move K#:L# into C##, then this code will do it also, but this time you don't have to first choose one of the cells involved (C#), you can simply click ANY cell in the row where you want the move to take place and the C and K:L pairs on that row will be swapped. Hope this is a little more understandable code for you to use. Sub SwapCellPairs() 'assumes you have selected ANY 'cell on the same row with the 'cells to be swapped ' 'will swap C## with K#:L# 'where # is the row number that 'you have selected a cell in ' 'the left column ID for left-most group Const group1Column = "C" ' change as needed Const group2Column = "K" ' change as needed Dim whatRow As Integer Dim firstValue As Variant ' temporary holding Dim secondValue As Variant ' temporary holding 'capture the row number we are on whatRow = ActiveCell.Row 'save the first group's values firstValue = Range(group1Column & whatRow).Value secondValue = Range(group1Column & whatRow).Offset(0, 1).Value 'move second group into 1st group's cells Range(group1Column & whatRow).Value = _ Range(group2Column & whatRow).Value Range(group1Column & whatRow).Offset(0, 1).Value = _ Range(group2Column & whatRow).Offset(0, 1).Value 'move the 1st group's saved values into 'the second 2nd group's cells Range(group2Column & whatRow).Value = firstValue Range(group2Column & whatRow).Offset(0, 1).Value = _ secondValue 'all done! End Sub "Graham Feeley" wrote: Ok first of all I thank you for your response and promptly I may say. Ok I explained it all wrong, I think u mean to move columns. It is rows I am working in and in fact it will be always the same I have columns A through to M I wish to move K16:L16 to the right 4 columns (or saved to variants) then move C16:C16 to K16:L16 then move K16:L16 to C16:C16 in other words it swaps 2 cells in the same row I thank you for your help and I have save your code for futher reading Regards Graham "JLatham" wrote in message ... Here you go: Sub SwapCellPairs() 'assumes you have selected the 'cell at the far left of a group 'to be swapped, and that the '"distance" between cells to be 'swapped is always the same ' 'how many columns from left-most of 'first group to first one in 2nd group? 'Example first group is in columns A and B 'second group is in E and F 'it is 4 columns from A to E Const firstGap = 4 Dim firstValue As Variant ' temporary holding Dim secondValue As Variant ' temporary holding 'save the first group's values firstValue = ActiveCell.Value secondValue = ActiveCell.Offset(0, 1).Value 'move second group into 1st group's cells ActiveCell.Value = _ ActiveCell.Offset(0, firstGap).Value ActiveCell.Offset(0, 1).Value = _ ActiveCell.Offset(0, firstGap + 1).Value 'move the 1st group over to where the '2nd group is ActiveCell.Offset(0, firstGap).Value = _ firstValue ActiveCell.Offset(0, firstGap + 1).Value = _ secondValue 'all done! End Sub To put the code in your workbook, open it up and press [Alt]+[F11] to open the vB Editor. In the VBE choose Insert -- Module and copy and paste the code above into that. Make any change you need to to the Constant value named firstGap. Close the VBE. Back in the worksheet view, choose Tools -- Macro -- Macros and highlight the name of the macro and use the [Options...] button to assign it a shortcut so that you can use it quickly. After that, all you have to do is select the left-most cell on a row wher you want to swap things and hit your shortcut key combination to swap the two groups. If you accidentally swap a wrong pair, just do it again to put them back the way they were. Enjoy! "Graham Feeley" wrote: Hi, to explain what I am trying to achieve is this. I have several columns and rows of data and I wish to move certain cells around in the same row. I have made a macro that moves 2 cells to a blank space, move 2 other cells into its cells and the moves the first 2 cells in its place however the macro alway returns to the cell address where I made the macro I would like it to commit what ever row i am in Hope u can understand what I am trying to achieve. 1 ONEMORENOMORE 3 DELAGO BOLT 3 GRAHAM 8 WILLIAM The macro in this case would swap n08 william with 3 Graham Regards Graham |
#6
|
|||
|
|||
Macro??
may be this ?
Sub swap() 'Rng = Cells(Rows.Count, "D").End(xlUp).Row 'For i = 2 To Rng i = 16 Range("K" & i & ":N" & i).Insert xlToRight S1 = Range("C" & i & "" & i).Value S2 = Range("K" & i & ":L" & i).Value Range("C" & i & ":d" & i) = S2 Range("K" & i & ":L" & i) = S1 'Next i End Sub On Aug 22, 9:36*am, "Graham Feeley" wrote: Ok first of all I thank you for your response and promptly I may say. Ok I explained it all wrong, I think u mean to move columns. It is rows I am working in and in fact it will be always the same I have columns A through to M I wish to move K16:L16 to the right 4 columns (or saved to variants) then move C16:C16 to K16:L16 then move K16:L16 to C16:C16 in other words it swaps 2 cells in the same row I thank you for your help and I have save your code for futher reading Regards Graham "JLatham" wrote in message ... Here you go: Sub SwapCellPairs() 'assumes you have selected the 'cell at the far left of a group 'to be swapped, and that the '"distance" between cells to be 'swapped is always the same ' *'how many columns from left-most of *'first group to first one in 2nd group? *'Example first group is in columns A and B *'second group is in E and F *'it is 4 columns from A to E *Const firstGap = 4 *Dim firstValue As Variant ' temporary holding *Dim secondValue As Variant ' temporary holding *'save the first group's values *firstValue = ActiveCell.Value *secondValue = ActiveCell.Offset(0, 1).Value *'move second group into 1st group's cells *ActiveCell.Value = _ * ActiveCell.Offset(0, firstGap).Value *ActiveCell.Offset(0, 1).Value = _ * ActiveCell.Offset(0, firstGap + 1).Value *'move the 1st group over to where the *'2nd group is *ActiveCell.Offset(0, firstGap).Value = _ * firstValue *ActiveCell.Offset(0, firstGap + 1).Value = _ * secondValue *'all done! End Sub To put the code in your workbook, open it up and press [Alt]+[F11] to open the vB Editor. *In the VBE choose Insert -- Module and copy and paste the code above into that. *Make any change you need to to the Constant value named firstGap. *Close the VBE. Back in the worksheet view, choose Tools -- Macro -- Macros and highlight the name of the macro and use the [Options...] button to assign it a shortcut so that you can use it quickly. After that, all you have to do is select the left-most cell on a row wher you want to swap things and hit your shortcut key combination to swap the two groups. *If you accidentally swap a wrong pair, just do it again to put them back the way they were. Enjoy! "Graham Feeley" wrote: Hi, to explain what I am trying to achieve is this. I have several columns and rows of data and I wish to move certain cells around in the same row. I have made a macro that moves 2 cells to a blank space, move 2 other cells into its cells and the moves the first 2 cells in its place however the macro alway returns to the cell address where I made the macro I would like it to commit what ever row i am in Hope u can understand what I am trying to achieve. 1 ONEMORENOMORE * * * * * * * * * * * * *3 DELAGO BOLT 3 *GRAHAM * * * * * * * * * * * * * * * * * * * * * * 8 WILLIAM The macro in this case would swap n08 william with 3 Graham Regards Graham |
Thread Tools | |
Display Modes | |
|
|