Rotating or cycling data

[IcemaNZ]

I have been searching and searching for a solution to this.

I need to make a row or column of data that cycles such as

The line is: 1 2 3 4 5 then when I trigger an event (date change) I want them to move across or down and the 5 moves back to the start.

So the second time the line reads: 5 1 2 3 4

A solution to this would be awesome because it takes forever to do manually.

Also can they be moved more than one cell eg. 3 cells (I.E. 1 2 3 4 5, then 3 4 5 1 2)


Cheers

 

[gsbelbin]

I've assumed that you have each of the numbers 1 to 5 in cells A2 to E2 and you have a header row above this with descriptions in A1 to E1. Try adding the following code to whatever event you have used.

Sub test123()
    Dim cell As Range
    Dim Temp(4) As Integer
 
    Range("A1").Select
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
 
    For Each cell In Selection
        Temp((cell.Column) Mod 5) = cell.Value
    Next cell
    For Each cell In Selection
        Cells(cell.Row + 1, cell.Column) = Temp(cell.Column - 1)
    Next cell
 
End Sub

If you want the figures to move across 3 spaces change the line

Temp((cell.Column) Mod 5) = cell.Value

to

Temp((cell.Column+2) Mod 5) = cell.Value

Note you only add 2 because the array Temp is zero based.