Swapping multiple cells accross a row

[John Corbin]

I am attaching a sample sheet as a reference.

in fact the sheet could contain hundreds if not thousands of rows.

Problem...

Look at column Player 1, the last 3 characters are AoS.
I want to find any rows where Player 1 has those three characters ( AoS ) and swap them with the contents of the Player 2.

BUT if that swap happens then the following swaps must occur as well:

Contents of Column "Side 1" swaps with Contents of Colum "Side 2"
Contents of Column "Player 1 Pts" swaps with Contents of Column "Player 2 Pts"

Ultimately I want this extended so that is the letters AoS appear in any of the following columns ( Player 4, Player 6, Player 8, Player 10)
the following swaps happens:

Contents of Column "Player 3" swaps with Contents of Column "Player 4"
Contents of Column "Side 3" swaps with Contents of Column "Side 4"
Contents of Column "Player 3 Pts" swaps with Contents of Column "Player 4 Pts"

Contents of Column "Player 5" swaps with Contents of Column "Player 6"
Contents of Column "Side 5" swaps with Contents of Column "Side 6"
Contents of Column "Player 5 Pts" swaps with Contents of Column "Player 6 Pts"

Contents of Column "Player 7" swaps with Contents of Column "Player 8"
Contents of Column "Side 7" swaps with Contents of Column "Side 8"
Contents of Column "Player 7 Pts" swaps with Contents of Column "Player 8 Pts"

Contents of Column "Player 9" swaps with Contents of Column "Player 10"
Contents of Column "Side 9" swaps with Contents of Column "Side 10"
Contents of Column "Player 9 Pts" swaps with Contents of Column "Player 10 Pts"

Appreciate any help

John

[Bob Phillips]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

DoSwap i, 3
For j = 16 To 27 Step 4

DoSwap i, j
Next j
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Private Sub DoSwap(ActiveRow As Long, ActiveCol As Long)
Dim tmp As Variant

With ActiveSheet.Cells(ActiveRow, ActiveCol)

If .Offset(0, 1).Value Like "*AoS" Then

tmp = .Offset(0, 1).Value
.Offset(0, 1).Value = .Offset(0, 3).Value
.Offset(0, 3).Value = tmp
tmp = .Value
.Value = .Offset(0, 2).Value
.Offset(0, 2).Value = tmp
End If
End With

End Sub
[/vba]

[John Corbin]

[vba]

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long, j As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To LastRow

DoSwap i, 3
For j = 16 To 27 Step 4

DoSwap i, j
Next j
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Private Sub DoSwap(ActiveRow As Long, ActiveCol As Long)
Dim tmp As Variant

With ActiveSheet.Cells(ActiveRow, ActiveCol)

If .Offset(0, 1).Value Like "*AoS" Then

tmp = .Offset(0, 1).Value
.Offset(0, 1).Value = .Offset(0, 3).Value
.Offset(0, 3).Value = tmp
tmp = .Value
.Value = .Offset(0, 2).Value
.Offset(0, 2).Value = tmp
End If
End With

End Sub
[/vba]

Great.. Thansk... Tried running it but I get a type mismatch on line:

LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row

[Bob Phillips]

I've just opened your workbook, plugged my code in, and it ran flawlessly.

[John Corbin]

I've just opened your workbook, plugged my code in, and it ran flawlessly.

I assigned your code to a button in a seperate sheet in the smae workbook.

Only other differance is that there is now much more data to work on.

[Bob Phillips]

But you are talking about a compile error, nothing to do with the amount of data.

When you say a button on another sheet, what sort of button?

[John Corbin]

But you are talking about a compile error, nothing to do with the amount of data.

When you say a button on another sheet, what sort of button?

From the Forms Toolbar

I was also gettng the error ion the VB Editor window When I ran the code

The error I get is "Run Time Error 13 Type Mismatch"

I have set watchs on all the vaiables and when it hits the line

[VBA]
LastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
[/VBA]

The values of teh watched variables become <out of Context>

[david000]

Start by changing this Const TEST_COLUMN As String = "Aos" back to
this:Const TEST_COLUMN As String = "A" you must have changed by mistake.

Also, that data is really hard to look at I can't tell what is supposed to be where...

[StephenR]

This has been cross-posted at Ozgrid. Can't provide the link as my post count is too small.

Thanks for wasting my time.

[John Corbin]

Start by changing this Const TEST_COLUMN As String = "Aos" back to
this:Const TEST_COLUMN As String = "A" you must have changed by mistake.

Also, that data is really hard to look at I can't tell what is supposed to be where...

Data cleaned up and AoS set back to A

I get no more run tim,e aerrors but it is also not doing what I need.

Note that the AoS ( see my original post ) is always the last 3 characters

[Bob Phillips]

This has been cross-posted at Ozgrid. Can't provide the link as my post count is too small.

Thanks for wasting my time.

Why I dropped off ... http://www.ozgrid.com/forum/showthread.php?t=91426

[John Corbin]

This has been cross-posted at Ozgrid. Can't provide the link as my post count is too small.

Thanks for wasting my time.

I posted it in Ozgrid to get a wider audiance... My apllogies