Swap identical ranges of cell values

[CCArtsAlliance]

I have a spreadsheet that lists the names and booth assignments (#s) of vendors at a festival. Because the vendor's booth assignments will be switched around as new vendors come in, I'd like a macro that would allow me to do something like this:

Select cell range in row 2
Select same-sized cell range in row 10
Swap their data.

Column A won't be swapped, as that contains the booth #s. I just want to switch the vendor's data between rows. If it helps there's currently only 3 columns for vendor data, but in the event more are added in the future, I was hoping the macro would accept any selection size so long as the second selection is the same # of cells.

Thank you! Oh, and I'm using Excel 2007.

[Palmetto]

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.

[CCArtsAlliance]

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.

[jaslake]

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

[Leith Ross]

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.

Please Login or Register  to view this content.

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.