Sorting data into multiple columns

[guibgirl86]

I have a batch of data in several columns of different information. One of the columns is a label for the team name "Red" or "Blue" I want to sort the information for each team in a separate columns for that team. I have the code that will copy/paste the information I want into the columns I want, but I am having trouble figuring out how to get the pasting into the new column to be successive.

dim x as long, y as long
For x = 2 To n Step 1 And y = 2
            If Cells(x, 3).Value = "Blue" Then Range("B" & x, "C" & x, "H" & x, "J" & x, "K" & x, "L" & x).Copy ("O" & y)
            If Cells(x, 3).Value = "Red" Then Range("B" & x, "C" & x, "H" & x, "J" & x, "K" & x, "L" & x).Copy ("W" & y)
        Next x

I have x to go down my main column of information, but I don't know how to call out y so that if O2 is filled then go to O3 and if W2 is filled then go to W3.

Any help is appreciated

 

[JLGWhiz]

I had to study your code for a few minutes to figure out the problem. Try substituting the following snippet for the one you posted. I assume that 'n' is defined eslewhere in the procedure.

dim x as long, y as long
y = 2
z = 2
For x = 2 To n Step 1
            If Cells(x, 3).Value = "Blue" Then 
                 Range("B" & x, "C" & x, "H" & x, "J" & x, "K" & x, "L" & x).Copy Range("O" & y)
                 y = y + 1 
            ElseIf Cells(x, 3).Value = "Red" Then 
                 Range("B" & x, "C" & x, "H" & x, "J" & x, "K" & x, "L" & x).Copy Range("W" & z)
                 z = z + 1
            End If
Next x

 

[guibgirl86]

Thanks
I think that solved my problem, but It keeps giving me an error about the range function, so i tried to replace it with the union function and it is still giving me problems.

For x = 2 To n Step 1 And y = 2 And z = 2
            If Cells(x, 2).Value = "At Sea" Then
                ActiveSheet.Union(Cells(x, 2), Cells(x, 3), Cells(x, 8), Cells(x, 10), Cells(x, 11), Cells(x, 12)).Copy (Range("O" & y)) And y = y + 1
            ElseIf Cells(x, 2).Value = "In Port" Then
                ActiveSheet.Union(Cells(x, 2), Cells(x, 3), Cells(x, 8), Cells(x, 10), Cells(x, 11), Cells(x, 12)).Copy (Range("W" & z)) And z = z + 1
        End If
        Next x

I keep getting the
Run Time error 1004
Method range of object_Global failed

 

[JLGWhiz]

You cannot use the 'And' Operator like this:

ActiveSheet.Union(Cells(x, 2), Cells(x, 3), Cells(x, 8), Cells(x, 10), Cells(x, 11), Cells(x, 12)).Copy (Range("O" & y)) And y = y + 1
ElseIf Cells(x, 2).Value = "In Port" Then
ActiveSheet.Union(Cells(x, 2), Cells(x, 3), Cells(x, 8), Cells(x, 10), Cells(x, 11), Cells(x, 12)).Copy (Range("W" & z)) And z = z + 1

The y = y + 1 and the z = z + 1 must go on a separate line in the code. See my June 26th Post.

 

[JLGWhiz]

Sometimes I just look right at a mistake and don't see it. But I have senility as an excuse. This code should clear up your issue.

Dim x As Long, y As Long, rng1 As Range
y = 2
Z = 2
For x = 2 To n Step 1
    Set rng1 = Union(Range("B" & x), Range("C" & x), Range("H" & x), Range("J" & x), Range("K" & x), Range("L" & x))
            If Cells(x, 3).Value = "Blue" Then
                 rng1.Copy Range("O" & y)
                 y = y + 1
            ElseIf Cells(x, 3).Value = "Red" Then
                 rng1.Copy Range("W" & Z)
                 Z = Z + 1
            End If
Next x