swap selected cells value and values to the left simultaneously.

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Sub Swap()
    If Selection.Count <> 2 Then
         MsgBox "Select 2 cells (only) to swap."
         Exit Sub
    End If
    Set trange = Selection
    If trange.Areas.Count = 2 Then
         temp = trange.Areas(2)
         trange.Areas(2) = trange.Areas(1)
         trange.Areas(1) = temp
    Else
         temp = trange(1)
         trange(1) = trange(2)
         trange(2) = temp
    End If
End Sub

Using the above code I able tp swap two selected cell values. However, I would like to add the function to also swap the Cell value to the left of selected cells. ex. IF Select Cell D53 and L34 and run the macro I not only what cell D53 and L34 value to swap but also value C53 and K34 without have to select C53 and K34. The values to the left of the cells selected will always be together. any help is greatly appreciated.

Thanks
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
965
Office Version
  1. 2016
Platform
  1. Windows
This is not completely modified your code since I'm not sure when the code under Else will be executed :), but you will get the idea

VBA Code:
Sub Swap()
    If Selection.Count <> 2 Then
         MsgBox "Select 2 cells (only) to swap."
         Exit Sub
    End If
    Set trange = Selection
    If trange.Areas.Count = 2 Then
         Temp1 = trange.Areas(2)
         Temp2 = trange.Areas(2).Offset(0, -1)
         trange.Areas(2) = trange.Areas(1)
         trange.Areas(2).Offset(0, -1) = trange.Areas(1).Offset(0, -1)
         trange.Areas(1) = Temp1
         trange.Areas(1).Offset(0, -1) = Temp2
    Else
         Temp = trange(1)
         trange(1) = trange(2)
         trange(2) = Temp
    End If
End Sub
 
Solution

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much Zot this works like magic :).
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Does that do what you want if the selection does not have two distinct areas?
not sure if I quite understand the question. but if I select D5 and D20 both values and the values to the left of selected cells do switch with Zot vba.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,373
Office Version
  1. 365
Platform
  1. Windows
not sure if I quite understand the question. but if I select D5 and D20 both values and the values to the left of selected cells do switch with Zot vba.
The question is: Does it do what you want if you select D5:D6 as a single range of two cells.
I am asking because your original code was written to deal with
Areas.Count = 2
or
Else ..

Also, what should happen if, say, C5 and D5 are selected, either as a single range of two cells or as two individual cells?
 

hajiali

Active Member
Joined
Sep 8, 2018
Messages
381
Office Version
  1. 2016
Platform
  1. Windows
so as a single range it does swap the values in D5:D6 but not the cells to the left but if I select them individual by holding "CTRL" the cells to the left will also switch, which is what I need. The values in column C will never be a cell to select it goes with cell values in column D. the user will only select two cells either 2 from column D or 2 from column L or 1 from D and one from L.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,373
Office Version
  1. 365
Platform
  1. Windows
if I select them individual by holding "CTRL" the cells
In that case I think this should be sufficient

VBA Code:
Sub Swap_v2()
    Dim Temp As Variant
    
    With Selection
      If .Count = 2 And .Areas.Count = 2 Then
        Temp = .Areas(1).Offset(, -1).Resize(, 2).Value
        .Areas(1).Offset(, -1).Resize(, 2).Value = .Areas(2).Offset(, -1).Resize(, 2).Value
        .Areas(2).Offset(, -1).Resize(, 2).Value = Temp
      Else
        MsgBox "Select 2 individual cells only."
      End If
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top