Rolling List in Excel

[Craig13_13]

Hi there

I'm sure it's a nice simple piece of code but I can't find it! I have a list of names on a sheet and am trying to make it a rolling rota, i.e. there is a name in a cell, you click a button and the name in the cell changes to the next name in the list, and when it gets to the bottom of the list it goes back to the top and works it's way down again, can anyone offer any help please?

Thanks!

 

[Peter_SSs]

Assuming ...
- the list is in column A, starting in row 2
- the 'rolling' list is to appear in cell C1
... then try this code for yout button.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> myval<br>    <SPAN style="color:#00007F">Dim</SPAN> myTarget <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    lr = Range("A" & Rows.Count).End(xlUp).Row<br>    <SPAN style="color:#00007F">Set</SPAN> myTarget = Range("C1")<br>    myval = myTarget.Value<br>    <SPAN style="color:#00007F">If</SPAN> myval = "" <SPAN style="color:#00007F">Then</SPAN><br>        myTarget.Value = Range("A2").Value<br>    <SPAN style="color:#00007F">Else</SPAN><br>        r = Range("A2:A" & lr).Find(What:=myval, LookIn:=xlValues, _<br>            Lookat:=xlWhole, MatchCase:=False, SearchFormat:=False).Row<br>        <SPAN style="color:#00007F">If</SPAN> r = lr <SPAN style="color:#00007F">Then</SPAN><br>            myTarget.Value = Range("A2").Value<br>        <SPAN style="color:#00007F">Else</SPAN><br>            myTarget.Value = Range("A" & r + 1).Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Domski]

My effort:

Sub test()
Dim findRange As Range
With Range("C1")
If .Value = "" Then
    .Value = Range("A1")
    
Else
    Set findRange = Range("A1:A10").Find(.Value, LookIn:=xlValues)
    
    If Not findRange Is Nothing Then
    
        If findRange.Offset(1, 0) = "" Then
        
            .Value = Range("A1")
            
        Else
        
            .Value = findRange.Offset(1, 0)
            
        End If
        
    End If
End If
End With
End Sub

Loops through the list in A1:A10 putting the value in C1.

Dom