vba - Excel macro button to change change cell values

Question

Any ideas on how to get a button to change cell values based on a table. Say cell A1 is the active cell and i want this value changed everytime I click the button based on the values in column B,just going down the list B1, B2,B3 etc...

Private Sub CommandButton1_Click()
    Range("A1").Value = Range("B1:B10").Value  
End Sub

Answer 1

I've assumed that you want to return to the B1 value once you've reached the B10 value and another click is made.

Private Sub CommandButton1_Click()
    If IsError(Application.Match(Range("A1").Value, Range("B1:B10"), 0)) Then
        Range("A1").Value = Range("B1").Value
    ElseIf Application.Match(Range("A1").Value, Range("B1:B10"), 0) = Range("B1:B10").Cells.Count Then
        Range("A1").Value = Range("B1").Value
    Else
        Range("A1").Value = Range("B1").Offset(Application.Match(Range("A1").Value, Range("B1:B10"), 0), 0).Value
    End If
End Sub