Need to swap a range of cells in Excel

[Mark McInerney]

Hi,

I have created a roster application, but on occasion I will need to swap hours rostered between employees.

My data is as follows:

I have 10 employees, and I capture data relative to their working day in 15 columns. I would like to be able to enter two employee names in 2 adjoining cells and hit a button that has the 'swap' macro assigned to it. So I need the employee name to stay as they are (no swap) and the data contained in the 15 adjoining cells to be swapped.

Is this possible?

Apologies in advance I'm not a programmer....

Hope somebody can help - Many Thanks.

 

[Mark McInerney]

I located this VBA - it looks close to my requirement, but I keep getting an error message.

The change will be that I am dealing with employees and not booths, and I will have 2 cells at the bottom of my roster that will have data validation (list on the employee table) and a button with swap hours and the correct macro assigned.

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

 

[John Davis]

Hi,

I have created a roster application, but on occasion I will need to swap hours rostered between employees.

My data is as follows:

I have 10 employees, and I capture data relative to their working day in 15 columns. I would like to be able to enter two employee names in 2 adjoining cells and hit a button that has the 'swap' macro assigned to it. So I need the employee name to stay as they are (no swap) and the data contained in the 15 adjoining cells to be swapped.

Is this possible?

Apologies in advance I'm not a programmer....

Hope somebody can help - Many Thanks.

Why can't the names be swapped to match the 15 columns? For me at least that would be easier.

 

[Mark McInerney]

There are several worksheets - one for each day, and a few for reports.