Creating cyclic pattern of numbers in Excel & VBA

[Renganathan]

Guys,
I have set of variables namely w,x, y,z & each variable have been assigned with some numbers.
Now I want to create a VBA program for doing some calculation. Inside the code i want to make the variables used in cyclic manner.

Say if i start with y, then next should be x,w,z & stop at that place. And instead of y if i start with w, then next should be z,y,x & stop at that point.

Hope i have written it clear. Please help me to do the same in excel/VBA

 

[Hui]

Renganathan

Firstly, Welcome to the Chandoo.org Forums

I will redirect your question to the following site that deals with it specifically
https://sites.google.com/site/e90e50fx/home/combinatorics-using-excel-formulas-and-examples

 

[Debraj]

Hi Renganathan..

I guess. You are looking for Loop within ARRAY.. but backward & Cyclick..

Can you please check the below code..

Sub test()
    Dim MyArray() As Variant
    'MyArray = Array("1", "2", "3", "4", "5", "6")
    NoOfArray = InputBox("Enter No of Array in MyArray", , 6) - 1
    ReDim MyArray(NoOfArray)
   
    For i = 0 To UBound(MyArray)
        MyArray(i) = InputBox("Enter Value # " & i + 1)
    Next i
   
    deb = InputBox("Enter array loopback position", , 3) - 1
   
    For i = deb To (deb - UBound(MyArray)) Step -1
        If i >= 0 Then
            CyclicPattern = CyclicPattern & "," & MyArray(i)
        Else
            CyclicPattern = CyclicPattern & "," & MyArray(UBound(MyArray) + 1 + i)
        End If
    Next i
   
    MsgBox "Expected CyclicPattern " & Chr(10) & _
        Mid(CyclicPattern, 2)
End Sub