Swap identical ranges of cell values

Why don't you simply change the value in the cell to reflect the reassigned booth number? You seem to want a more complicated solution than what is required by your explanation, unless there is more to this than what you detailed.

That would work, yes. I was trying to avoid having to re-sort the list every time a number changed, though, since I won't be the only one working with this list in its final stages and not everyone involved is as familiar with excel.

I have a broader interest in the macro beyond this workbook anyway; there've been a time or two when being able to flip around cells would've been handy, but I end doing it the old fashioned way.

Hi CCArtsAlliance

Post a sample of what you have and what you'd like it to be and I'll be glad to look at it with you.

John

Hello CCArtsAlliance,

Welcome to the Forum!

This macro will do what you asked. Very simple to use. The user inputs 2 booth numbers separated by either a space or comma and clicks "OK" or preses the "Enter" key to switch them. The macro expects the booth numbers to be in column "A". The other information starts at column "B" on to the last column header in row 1.

Copy this macro into a VBA module. Add a command button to the sheet and attach this macro to it. When the user clicks the button, he or she will be prompted to enter the booth numbers. An example workbook is attached.

'Thread: http://www.excelforum.com/excel-programming/728960-swap-identical-ranges-of-cell-values.html
'Poster: CCArtsAlliance
'Posted: May 25, 2010
'Author: Leith Ross

Sub SwitchBooths()

  Dim Booths As Variant
  Dim Booth1 As Variant
  Dim Booth2 As Variant
  Dim ColCount As Long
  Dim Msg As String
  Dim Temp As Variant
  
    ColCount = Cells(1, Columns.Count).End(xlToLeft).Column - 1
    
EnterBooths:
    Booths = InputBox("Enter the 2 booths you want to switch." & vbCrLf & "Click OK or press Enter when you are done.")
    If Booths = "" Then Exit Sub
    
      If Booths Like "*,*" Then
         Booths = Split(Booths, ",")
      Else
         If Booths Like "* *" Then Booths = Split(Booths, " ")
      End If
      
      If VarType(Booths) = 8 Then
         MsgBox "Please enter 2 booth numbers separated by a space or comma."
         GoTo EnterBooths
      End If
         
        With Columns("A")
          .EntireColumn.NumberFormat = "@"
          Booth1 = Trim(Booths(0))
            Set Booth1 = .Cells.Find(Booth1, , xlValues, xlWhole, xlByRows, xlNext, False)
            If Not Booth1 Is Nothing Then
               Set Booth1 = Booth1.Offset(0, 1).Resize(1, ColCount)
            Else
               Msg = Booths(0) & ","
            End If
          Booth2 = Trim(Booths(1))
            Set Booth2 = .Cells.Find(Booth2, , xlValues, xlWhole, xlByRows, xlNext, False)
            If Not Booth2 Is Nothing Then
               Set Booth2 = Booth2.Offset(0, 1).Resize(1, ColCount)
            Else
               Msg = Msg & Booths(1) & ","
            End If
          .EntireColumn.NumberFormat = "General"
       End With
    
     If Msg <> "" Then
        MsgBox "Did Not Find " & Left(Msg, Len(Msg) - 1)
     Else
        Temp = Booth2.Value
        Booth2.Value = Booth1.Value
        Booth1.Value = Temp
     End If
     
End Sub

Adding the Macro

1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
   
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

Adding Buttons to the Worksheet

1. Click View on the Excel Menu Bar or use ALT+V.
2. Click Toolbars or type T to display the available toolbars.
3. Click on Forms or use the Down Arrow to select this toolbar.
4. Look for the rectangular icon. When you place the mouse on it you will see Button displayed.
5. Click on this icon.
6. Move the cursor to the cell where you want the button. Left Click and Hold the button down while you move the mouse. This will draw the button. Release the button when you are done.
7. A dialog box will appear asking you to assign a macro. Close the dialog. You can add the macro later.
8. Repeat steps from 4 on to add more buttons.