Loop in Excel

  • Thread starter Thread starter Khan
  • Start date Start date
K

Khan

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
 
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
Click to expand...

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
 
This goes in the ThisWorkbook code module. Open the VBE and in the Project
pane, double click on ThisWorkbook to open the code module then copy and
paste this code into it.

Private Sub Workbook_Open()
If Weekday(Date) <> 2 Then
Exit Sub
ElseIf Range("IV1") = Date Then '<<<Change cell if desired.
Exit Sub
Else
Range("A10").Cut
Range("A1").Insert Shift:=xlDown
Range("IV1") = Date '<<<Change to match the other.
End If
ThisWorkbook.Save
End Sub


I used Range("IV") as the check range so that if the file is opened more
than once a day, it will not change again that day. If first checks to see
if it is Monday, If it is not Monday it exits the procedure without doing
anything.
The requirement for this to work is that your system date is correctly set
and that it uses Sunday as day one of the week.
 
It occurred to me that you probably have more than one sheet in your
workbook and that it might not be sheet 1 that contains the data, so I added
a couple of lines that you can modifiy if needed to make sure the right
sheet is executing.

Private Sub Workbook_Open()
Dim sh As Worksheet
Set sh = Sheets("Sheet1") '<<<Change to actual sheet.
If Weekday(Date) <> 2 Then
Exit Sub
ElseIf sh.Range("IV1") = Date Then '<<<Change cell if desired.
Exit Sub
Else
sh.Range("A10").Cut
sh.Range("A1").Insert Shift:=xlDown
sh.Range("IV1") = Date '<<<Change to match the other.
End If
ThisWorkbook.Save
End Sub
 
Back
Top