# Binomial tree node looping

#### BJBeans

##### New Member
Hi I am trying to work out a simple way to loop through what is effectively a binomial option tree.

2
So it starts with node (1) that then branches off
3

The tree is of indeterminate size, and some nodes will end before others. So I effectively need to loop through it just printing the value if there is one down each option path, and stop looping when all the paths have been run.

I was originally thinking something like
Sub Loop()
Dim Count as String

Do While ActiveCell.Value <>Empty
For Count = -1 To 1
ActiveCell.Value = ActiveCell.Value *2 `Or any other action just to show that it has been looped through
ActiveCell.Offset(Count, 1)

Loop

But that just seems to run off in one direction. So the issue I'm having is telling the loop to explore all possible paths.

Thank you.

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Welcome to the board.

Can you show an example of the tree data structure on the worksheet and the expected traversal?

In what way does the tree "branch off?"

I intended to write Dim Count as Integer in my declaration, just FYI. Don't want to get distracted from the core problem. Thank you.

So effectively you start at 1 and then can progress to either 2 or 3, and from each of those new nodes you would have 2 options. But all will eventually end (I've put Null in the table below just to highlight that there is no option there, but in reality would just be a blank cell).

So more just a learning exercise than anything for me in how to write code that loops through different potential paths.
 Start Step1 Step2 Step3 Null 4 Null 2 7 1 5 Null 3 8 6 Null 9 10

<tbody>
</tbody>

How about a nice random walk?

Code:
``````Sub BJB()
Dim iRow As Long
Dim iCol As Long

Columns.ColumnWidth = 2
Cells.Interior.Color = vbWhite
iRow = 10
iCol = 1
Randomize

Do
Cells(iRow, iCol).Interior.Color = vbBlue
iCol = iCol + 1
If Rnd > 0.5 Then iRow = iRow + 1 Else iRow = iRow - 1
Loop While iRow > 0 And iCol < Columns.Count
End Sub``````

Thank you! That is instructive!

You're welcome.

Replies
3
Views
245
Replies
2
Views
1K
Replies
8
Views
2K
Replies
1
Views
589
Replies
1
Views
390

1,206,814
Messages
6,075,031
Members
446,114
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back