Macro??

  • Thread starter Thread starter Graham Feeley
  • Start date Start date
G

Graham Feeley

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
 
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!
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
Perhaps I'm reading this wrong. Reread it and see if you can provide an
explanation.
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
Click to expand...

If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.



--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Graham Feeley said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
[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
C16:D16.

Anyhow, give that you want to move K#:L# into C#:D#, 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:D 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#:D# 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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 & ":D" & 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



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


Here you go:
Click to expand...
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
Click to expand...
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.
Click to expand...
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.
Click to expand...
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.

"Graham Feeley" wrote:
Click to expand...
Click to expand...
 
Back
Top