recursive loop with unknow number of levels

[bolo]

HI all you experts, Need a bit of a hand defining a macro structure.

What i am trying to come up with is a macro that will do the following.

Imagine i have the following data

3,2,3

What i am looking to do, is to decrease the right most number down to 1, then decrease the the next left number by 1, reset the right most number and decrease again. I need this process to reduce all the numbers to 1.eg

3,2,2
3,2,1
3,1,3
3,1,2
3,1,1
2,2,3
2,2,2
2,2,1
2,1,3
etc...

However the problem is, that the number of levels (3 in this example), can vary. Obviously if it was just 3 levels i can just use nested if statements, but as i do not know how many levels it is i can't use nested ifs.

The background to this is i am doing a spiderweb type nearest neighbour search. so if the first neighbour is good, i end the search. If the first neighbour is not good, i need to branch out to the next ones and so on. Therefore i do not know at the start how many loops i need to compute. SO i need to come up with a recursive type loop to control this. Not 100% sure how to do this in a vba macro so any pointers gratefully received.

Regards

Bolo

 

[bolo]

A gentle bump... I have spent the day do my head in with numerous do loops.... Just can't get my head around it!!!! Can anyone get me started?

 

[pgc01]

Hi Bolo

Not sure I understood completely, but try running this code:

Sub Test()
Dim j As Long, lRow As Long
Dim bStop As Boolean
Dim vArr As Variant, vArr1 As Variant
 
' vArr = Array(3, 2, 3)
vArr = Array(2, 3, 2, 4)
vArr1 = vArr
 
Do
    lRow = lRow + 1
    Range("A" & lRow).Resize(1, UBound(vArr) - LBound(vArr) + 1).Value = vArr1
    For j = UBound(vArr) To LBound(vArr) Step -1
        If vArr1(j) > 1 Then vArr1(j) = vArr1(j) - 1 Else vArr1(j) = vArr(j)
        If vArr1(j) <> vArr(j) Then Exit For
        If j = LBound(vArr) Then bStop = True
    Next j
Loop While Not bStop
    
End Sub

This code writes the result starting in A1, one value per column.

 

[bolo]

You sir are a gent.

I will try modifying this to see if i can get my procedure to work

Thanks

Bolo

Hi Bolo

Not sure I understood completely, but try running this code:

Sub Test()
Dim j As Long, lRow As Long
Dim bStop As Boolean
Dim vArr As Variant, vArr1 As Variant
 
' vArr = Array(3, 2, 3)
vArr = Array(2, 3, 2, 4)
vArr1 = vArr
 
Do
    lRow = lRow + 1
    Range("A" & lRow).Resize(1, UBound(vArr) - LBound(vArr) + 1).Value = vArr1
    For j = UBound(vArr) To LBound(vArr) Step -1
        If vArr1(j) > 1 Then vArr1(j) = vArr1(j) - 1 Else vArr1(j) = vArr(j)
        If vArr1(j) <> vArr(j) Then Exit For
        If j = LBound(vArr) Then bStop = True
    Next j
Loop While Not bStop
    
End Sub

This code writes the result starting in A1, one value per column.