Selection Process finding "x"s from within 7 columns

Hi I have a process in which I have to create a selection without bias. There are 9 categories in this selection process. The 1st and 2nd category already have code and they work differently from the rest. For the 3rd through the 8th categories I have to choose one from the 3rd category, then one from the 4th, etc. After the 8th category I start back at the 3rd and go to the 4th, ect. It ends when cell A1 reaches 30%.

Its a little difficult to explain but the process is simple enough to understand once you see it. I have attached an example of the process. I have also started the code and left explanations within the code.

Sub selection_process()

Dim x As Range, lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
On Error Resume Next

'Sort Column L, Descending, and N, Ascending with a header row:
Range("A2:AZ" & lastRow).Sort Key1:=Range("L2"), Order1:=xlDescending, key2:=Range("N2"), _
order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Place a "1" in SEL column for each row which containS an "x" in column C.
For Each x In Range("C2:C" & lastRow)
    If x.Value = "x" Then
       x.Offset(0, -1) = "1"
    End If
Next x

''''''''''''START LOOP'''''''''''''''''''''''''''''''''''''''''''''''''''''''

'start at cell E2 and go down to the first "x" in the column.
'If column B of same row contains a number, go to next "x" below in column E
'if column B of same row equals nothing, place a "3" in column B

'start at active cell in E and go right one column and then down to the first "x" in the column.
'If column B of same row contains a number go to next "x" below in column F
'if column B of same row equals nothing, place a "4" in column B

'start at active cell in F and go right one column and then down to the first "x" in the column.
'If column B of same row contains a number, go to next "x" below in column G
'if column B of same row equals nothing, place a "5" in column B

'start at active cell in G and go right one column and then down to the first "x" in the column.
'If column B of same row contains a number go to next "x" below in column H
'if column B of same row equals nothing, place a "6" in column B

'start at active cell in H and go right one column and then down to the first "x" in the column.
'If column B of same row contains a number, go to next "x" below in column I
'if column B of same row equals nothing, place a "7" in column B
 
'start at active cell in I and go right one column and then down to the first "x" in the column.
'If column B of same row contains a number, go to next "x" below in column J
'if column B of same row equals nothing, place an "8" in column B

'Then start the process over starting with '''''''start loop''''' above.

'Continue the process until the formula in A1 reaches 30% (it will probably not be right at 30%, give or take a few percent)

'''''''''''''END LOOP''''''''''''''''''''''''''''''''''''''''''''''''''''''''

End Sub

I'm hoping there are just a few lines of code that I can loop until the cell A1 reaches 30%.

Please help!
Thank you!
Curbster

Hi Curbster,

A few questions...

1. In your pseudo-code, you say for each part of the loop "Start at cell D2", "Start at cell E2", etc. but your example spreadsheet shows gray boxes for the first round of selection don't follow that pattern. It looks like you start at D2 and find the first "x" below that, then you go over to column E in that row and find the first "x" from there downward, then move to column F from that row and find the first "x". If you truly wanted to start at the top of each column and find the first "x" from the top, I would expect to see D32, E5, F2, G2, H2, I2 and J2 selected in the first round. Am I correct in my observation?

2. What happens when you get to the "last row" in a column and 30% has not yet been reached? Do you start back at the top of that column? (This assumes my observation in question 1 was correct, and that you're not simply starting at D2, E2, F2, etc. every step through the loop.

Thanks.

Paul,
you are exactly right! My mistake. So once a selection is made, go to the next column (same row) and head downwards. Lets say I just selected a 6, I would go over to column 7, and then down to the first x. If I hit the bottom and have not found an x in that column, I start back at the top (in the same column) until I find the next 7. If there are no 7's left, I go on to the next column, column 8.

Thanks for helping me clear this up. I'll edit it above as well.

is it even possible to write code to do this?
Thanks!

I appreciate your help! Its close. Is there any way to get it to start back at the top after every round? For example column 3 starts the next X down from the top instead of the next x down from the last x in column 8?

Also, I tried deleting the selection numbers and running the code, it only got me to 22%. I think it is because it is not starting over at the top. If I reach the bottom before I finish an 8 selection, I start back at the top and grab the first x in column 8 (unless it has already been selected by another number). I do this for all columns. Another example, if after the selection has started and I run out of 3's, I go on to the 4's. So I would start at 4, then go right to 5, 6, 7, 8, and back to 4, 5, 6... etc.

I tried to figure out the code but I'm no good at creating the code yet. Thank you so much for your help!

Ok, I got most of it working for how I need, now it just needs to keep looping back to the top until cell A1 equals around 32% (currently it only reaches 13.52%).

once you have run the macro, see the comment in cell J141 to explain.

Sub selection_process()

Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Rows.Count
On Error Resume Next

'Sort Column L, Descending, and N, Ascending with a header row:
Range("A2:AZ" & lastRow).Sort Key1:=Range("L2"), Order1:=xlDescending, key2:=Range("N2"), _
order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Dim onex As Range
'Place a "1" in SEL column for each row which containS an "x" in column C.
For Each onex In Range("C2:C" & lastRow)
    If onex.Value = "x" Then
       onex.Offset(0, -1) = "1"
    End If
Next onex

Dim twox As Range

'Place a "2" in SEL column for each row which containS an "x" in column D.
'skip any cell within column B that already contains a value of 1.
For Each twox In Range("D2:D" & lastRow)
    If twox.Value = "x" And twox.Offset(0, -2) = "" Then
       twox.Offset(0, -2) = "2"
    End If
Next twox

'selects x's in column 3 through column 8 in a certain order.
        'this is not looping right yet.
Dim a As Long, b As Long, c As Long, y As Long
y = Cells(Rows.Count, 1).End(xlUp).Row
b = 5
c = 4
    For a = 2 To y
    If Cells(1, 1).Value < 0.32 Then
        If Cells(a, b) = "x" And Cells(a, 2) = "" Then
              Cells(a, 2) = b - 2
                Cells(a, b).Interior.ColorIndex = c
                b = b + 1
               
                If b = 11 Then
                    b = 5
                    c = c + 1
                    End If
        End If
        Else
        Exit For
        End If
    Next a
    MsgBox "complete"
    End Sub

thanks for your help!