Hi All,

Really appreciate some help here....I'm stuck!

I found this code on the net and have been trying to amend it, without success, to suit my requirements.

Instead of booths, i have employee names, and to the right of the employee name, I have several columns of data that I want to be able to switch. I want the employee and the next 3 columns to remain static, but from column 4 to 7, I want these cells swapped.

In summary when the code runs, it creates a dialog/inpuit box that allows me to enter 2 employee names, and swap the contents of cells to the right of those names.

What I want to do is change the input method. I want to have 2 cells at the bottom of my spreadsheet that will have a data validation list (all my employee names), be smart enough not to fall over if the user in error selects the same name from the drop down list for both cells, and will then swap the values that are in the 4th through to the 7th column - how do I tell the code to swap only from the 4th column to the 7th?


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

Many thanks in advance if anybody can help me on this one.

Thanks.