decision tree

[jklingeljobs]

I am hoping to figure out how to loop through a decision tree. I want to have a code to randomly pick through column A and chose a random value. take this value and put into "results" tab a1. Then move to column b in sheet 1, and only chose values that are associated with what was chosen in the first decision and put this into "results" a2. Then move the column c in sheet 1, chose only values that are part of the set, etc.. and put into "results" a3.

At the end I will have a decision of one possible route through my decision tree.

example- start - picks 2 from column a, then moves and picks d, then moves and picks 2a. This is it.. so in "results" I will see that I chose 2,d,2a..

thank you so much!!!!

[p45cal]

Several assumptions have been made, among them:

• That there's nothing anywhere in column 1 of Sheet1 but the section starts.
• There is at least one totally blank row between sections
• There are no blanks within the columns of each section (except at the bottom)
• That each cell in each section's top row has a value in it.

Try:
[vba]Sub blah()
Dim Route()
ReDim Route(1 To 1)
With Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
For Each colm In .Areas(Int(.Areas.Count * Rnd + 1)).CurrentRegion.Columns
Route(UBound(Route)) = colm.Cells(Int(colm.SpecialCells(xlCellTypeConstants).Cells.Count * Rnd + 1)).Value
ReDim Preserve Route(1 To UBound(Route) + 1)
Next colm
End With
ReDim Preserve Route(1 To UBound(Route) - 1)
Sheets("results").Cells(Sheets("results").Rows.Count, 1).End(xlUp).Offset(1).Resize(UBound(Route)) = Application.Transpose(Route) 'vertical output
'Sheets("results").Cells(Sheets("results").Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(Route)) = Route 'horizontal output
End Sub
[/vba]It may help understanding if I include a longer development version:[vba]Sub blah()
Dim Route()
ReDim Route(1 To 1)
With Sheets("Sheet1").Columns(1).SpecialCells(xlCellTypeConstants)
x = Int((.Areas.Count ) * Rnd + 1)
' .Areas(x).CurrentRegion.Select
With .Areas(x).CurrentRegion
For Each colm In .Columns
'colm.Select
y = colm.SpecialCells(xlCellTypeConstants).Cells.Count
yy = Int(y * Rnd + 1)
Debug.Print colm.Cells(yy) & ",";
Route(UBound(Route)) = colm.Cells(yy).Value
ReDim Preserve Route(1 To UBound(Route) + 1)
Next colm
ReDim Preserve Route(1 To UBound(Route) - 1)
With Sheets("results")
.Cells(.Rows.Count, 1).End(xlUp).Offset(1).Resize(, UBound(Route)) = Route
End With
End With
End With
Debug.Print
End Sub[/vba]

[Bob Phillips]

Why don't you just use a RAND function in results tgo generate a random row number and pick up that from column A, then use VLOOKUP to get the rest. Simpler than VBA.

[p45cal]

Why don't you just use a RAND function in results tgo generate a random row number and pick up that from column A, then use VLOOKUP to get the rest. Simpler than VBA.

Not with you there. I read that the OP wanted a random selection from subsequent columns in each section. Could you give an example?

[Bob Phillips]

I read this bit ... I want to have a code to randomly pick through column A and chose a random value ... as simply wanting a random value in column A.

[mikerickson]

I'm having trouble understanding what options go with what previous choices.

It looks that if the 1 is chosen as the Column A value, then the next choice would be between a,b; if 2 were chosen then second choice would be between c,d, etc.

At this point, I loose track,

If the first choice is 1 and the second choice is b, what are the options for the third choice?