# Rotating a list

I have a closed loop list like

loop(1)=      19
loop(2)=      33
loop(3)=      32
loop(4)=      31
loop(5)=      6
loop(6)=      10
loop(7)=      11
loop(8)=      19

I would like to have a macro which will find the minimum number (here it is 6) and rotate the list so that the list starts with that number giving

loop(1)=      6
loop(2)=      10
loop(3)=      11
loop(4)=      19
loop(5)=      33
loop(6)=      32
loop(7)=      31
loop(8)=      6
LVL 43
###### Who is Participating?

Commented:
Collection rotating sub:
``````Sub RotateLoop(vLoop As Collection)
Dim dMin As Double
Dim i As Long, iMin As Long, n As Long
n = vLoop.Count
dMin = 1E+307
For i = 1 To n
If vLoop(i) < dMin Then
iMin = i
dMin = vLoop(i)
End If
Next
If iMin > 1 Then
vLoop.Remove n
For i = 1 To iMin
If i < iMin Then vLoop.Remove 1
Next
End If
End Sub
``````
0

Senior Infrastructure Support Analyst & Systems DeveloperCommented:
Where did you store the numbers in the list?
Where do you want the output list?
0

Commented:
There may be a more elegant way of doing it, but here is code that uses an intermediate array for rewriting the list.
``````Sub Test()
Dim vLoop(1 To 8) As Variant, vResult As Variant
Dim i As Long
vLoop(1) = 19
vLoop(2) = 33
vLoop(3) = 32
vLoop(4) = 31
vLoop(5) = 6
vLoop(6) = 10
vLoop(7) = 11
vLoop(8) = 19
RotateLoop vLoop
For i = 1 To 8
Debug.Print i & "    " & vLoop(i)
Next
End Sub

Sub RotateLoop(vLoop As Variant)
Dim dMin As Double
Dim v As Variant
Dim i As Long, iMin As Long, iFirst As Long, iLast As Long, j As Long, k As Long
iFirst = LBound(vLoop)
iLast = UBound(vLoop)
ReDim v(iFirst To iLast)
dMin = Application.Min(vLoop)
iMin = Application.Match(dMin, vLoop, 0) + iFirst - 1
j = iFirst
For i = iMin To iLast
v(j) = vLoop(i)
j = j + 1
Next
If iMin > iFirst Then
For i = iFirst To iMin - 1
v(j) = vLoop(i)
j = j + 1
Next
End If
For i = iFirst To iLast
vLoop(i) = v(i)
Next
End Sub
``````
0

EngineerAuthor Commented:
Thanks, Brad, but the result does not match the desired result. The original loop starts at 19 and ends at 19. The transformed loop should start at 6 and end at 6.

Would it be also possible to have vLoop as a collection object?
0

Commented:
I can add the "repeat the starting value at end of list" feature by adding two statements to the original code:
``````Sub RotateLoop(vLoop As Variant)
Dim dMin As Double
Dim v As Variant
Dim i As Long, iMin As Long, iFirst As Long, iLast As Long, j As Long, k As Long
iFirst = LBound(vLoop)
iLast = UBound(vLoop)
ReDim v(iFirst To iLast)
dMin = Application.Min(vLoop)
iMin = Application.Match(dMin, vLoop, 0) + iFirst - 1
j = iFirst
For i = iMin To iLast - 1
v(j) = vLoop(i)
j = j + 1
Next
If iMin > iFirst Then
For i = iFirst To iMin - 1
v(j) = vLoop(i)
j = j + 1
Next
End If
v(iLast) = v(iFirst)
For i = iFirst To iLast
vLoop(i) = v(i)
Next
End Sub
``````
Before I embark upon modifying the code to work with a collection, could you be more specific about which property of the objects in the collection should be minimized?
0

EngineerAuthor Commented:
I am not sure about what you mean by
which property of the objects in the collection should be minimized?

I have something like

Sub Test()
Dim vLoop As New Collection
Dim vResult As Variant
Dim i As Long
RotateLoop vLoop
For i = 1 To 8
Debug.Print i & "    " & vLoop(i)
Next
End Sub
0

EngineerAuthor Commented:
Perfect, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.