PDA

View Full Version : decision tree



jklingeljobs
12-04-2009, 06:49 PM
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
12-05-2009, 03:30 AM
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:
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
It may help understanding if I include a longer development version: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

Bob Phillips
12-05-2009, 03:43 AM
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
12-05-2009, 04:29 AM
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
12-05-2009, 05:55 AM
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
12-06-2009, 12:05 PM
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?