Loop in Excel
On 25 Apr, 23:39, Khan wrote:
In the Column "A" I have 10 Names and In the Column "B" I have
1,2,3........10 (as below) in Excel sheet. I want that every week Last
Name (Ebie) should come at the place of number one (Paul) *In the
column A and rest of the all names should go down respectively. Every
week Bottom Name should Come at the place of Top Name. *The Column B
will remain the same but name should be change every week
automatically last at the place of first and first at the place of
second and so on. How I can do it in Excell Programming.
Column A * * * * * * * * * * * * * * * * * Column B
Paul * * * * * * * * * * * * * * * * * * * * * * *1
Mick * * * * * * * * * * * * * * * * * * * * * * *2
Terry * * * * * * * * * * * * * * * * * * * * * * *3
Joan * * * * * * * * * * * * * * * * * * * * * * *4
Justin * * * * * * * * * * * * * * * * * * * * * * 5
Jim * * * * * * * * * * * * * * * * * * * * * * * *6
Jo * * * * * * * * * * * * * * * * * * * * * * * * *7
Kay * * * * * * * * * * * * * * * * * * * * * * * *8
Chuck * * * * * * * * * * * * * * * * * * * * * *9
Ebie * * * * * * * * * * * * * * * * * * * * * * * 10
Hi Khan.
For a possible solution, try:
Public Sub prova()
Dim arr(1 To 11) As String, idx As Long
Dim rng As Range, cl As Object
Set rng = Range("A1:A10")
idx = 2
For Each cl In rng
arr(idx) = cl.Value
idx = idx + 1
Next
arr(1) = arr(11)
idx = 1
For Each cl In rng
cl.Value = arr(idx)
idx = idx + 1
Next
End Sub
Regards,
Eliano
|