Selection Process finding "x"s from within 7 columns

**curbster** · 09-10-2009, 09:28 PM

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