Binomial tree node looping

[BJBeans]

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.

 

[shg]

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?"

 

[BJBeans]

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.

 

[BJBeans]

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>

 

[shg]

How about a nice random walk?

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

 

[BJBeans]

Thank you! That is instructive!

 

[shg]

You're welcome.