Another Macro Please

[Dazzawm]

Is there any way that when I select a range of cells the data will swap places?

i.e if i select the range A1:J5 I want it to change the data over as below on the same sheet


Before

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD> </TD><TD> </TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD></TR></TBODY></TABLE>

After

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 67px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"><COL style="WIDTH: 45px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>I</TD><TD>J</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD>GOODBYE</TD><TD> </TD><TD> </TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD><TD>HELLO</TD></TR></TBODY></TABLE>

 

[JamesW]

Hi,

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:D5")) Is Nothing Then
        oldVal = Target.Value
        Target.Value = Target.Offset(0, 6).Value
        Target.Offset(0, 6).Value = oldVal
    ElseIf Not Intersect(Target, Range("G1:J5")) Is Nothing Then
        oldVal = Target.Value
        Target.Value = Target.Offset(0, -6).Value
        Target.Offset(0, -6).Value = oldVal
    End If
End Sub

 

[MickG]

If you select any cell within your range the cells will change over.:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Temp
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("A1:J5")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng1 = Range("A1:D5")
        [COLOR="Navy"]Set[/COLOR] Rng2 = Rng1.Offset(, 6)
            Temp = Rng1.value
                Rng1 = Rng2.value
                    Rng2 = Temp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

Thanks, both of these codes look like they are for the cells I have put the example as. I did say that I wanted the macro to run on the cells i had selected they could be J850:P2000. Also does it matter if I have data in cells either side?

 

[MickG]

The below code will work as long as the number of columns selected is "Even".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_SelectionChange(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng1 [COLOR="Navy"]As[/COLOR] Range, Temp
[COLOR="Navy"]Dim[/COLOR] Rng2 [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]If[/COLOR] Target.Columns.Count > 1 And Target.Columns.Count Mod 2 = 0 [COLOR="Navy"]Then[/COLOR]
     [COLOR="Navy"]Set[/COLOR] Rng1 = Target.Resize(, Target.Columns.Count / 2)
       [COLOR="Navy"]Set[/COLOR] Rng2 = Rng1.Offset(, Target.Columns.Count / 2)
            Temp = Rng1.value
                Rng1 = Rng2.value
                    Rng2 = Temp
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Dazzawm]

When I go to view macro and run it is not there!? I have copied it in as I normally do.

 

[MickG]

I should open the Vb editer, Remove old code if any and paste new code in.
You can't have 2 of the same "Event" Codes in the same Worksheet.
Though it not too clear where its gone.
Mick

 

[Dazzawm]

I have no macros in there at all and still cant see it.

 

[Dazzawm]

I think there must be something wrong with this macro as I have tried it on another PC and it still not visible when you go to run it.

 

[syntaxed]

This macro is marked as "Private" which means it won't be visible to you when you go to View Macros to run it. Instead, it'll run automatically whenever the selection changes on the worksheet.