Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in Excel
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. "Khan" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop in Excel
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 "Khan" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
returning back to loop check condition without completing the loop | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Naming Worksheets - Loop within a loop issue | Excel Programming | |||
(Complex) Loop within loop to create worksheets | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming |