Rotate a list

[b9k9uk]

Hello,

Using the code:

Sub Rotate_Rota()
' cn = cell number
' fc = first cell
' lc = last cell


Dim cn As Integer, fc As Integer, lc As Integer

cn = 1

Do

cn = cn + 1

If Cells(cn, 1).Value = "fc" Then

Do

cn = cn + 1

If Cells(cn, 1).Value <> "lc" Then

Cells(cn, 1).Copy Cells(cn, 17)

End If

Loop Until Cells(cn, 1).Value = "lc"

End If

Loop Until Cells(cn, 1).Value = "lc"

Range(Cells(fc + 1, 17), Cells(lc - 2, 17)).Copy Cells(fc + 2, 17)

End Sub

It strikes me a bit long winded but bear with me please, the object is take the name at the bottom of the list and place it at the top of the list. When I get to the last line in the code I get an error 1004 Application-defined or object-defined error.

What does this mean and is there a way around it?

The list can vary in length hence the need to define where it starts and ends. Is there an easier way to do this.

Many thanks in advance for any suggestions help.

 

[StephenCrump]

You have defined fc and lc as integer, but haven't assigned any values.

Therefore Cells(lc - 2, 17) is Cells(-2,17) which is causing the error message.

The easier way / way around it depends on what you are trying to do (?) which isn't clear from the code you've posted.

 

[MickG]

Perhaps:-
For data in column "A"

[COLOR="Navy"]Sub[/COLOR] MG17Aug29
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    Lst = Range("A" & Rows.Count).End(xlUp).Row
    Cells(Lst, 1).Cut
    Cells(1, 1).Insert shift:=xlDown
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[b9k9uk]

You have defined fc and lc as integer, but haven't assigned any values.

Therefore Cells(lc - 2, 17) is Cells(-2,17) which is causing the error message.

The easier way / way around it depends on what you are trying to do (?) which isn't clear from the code you've posted.

Stephen Many thanks for your reply.

Yes you are correct I failed to assign a value to fc and lc which I have now done and it works, again many thanks for taking the trouble to reply.

 

[b9k9uk]

MickG and Stephen

The aim has been achieved, many thanks.

To explain, a list of names work a rota on a weekly basis. Each week the name at the bottom of the rota is moved to the top.

So Tom, ****, Harry on week 1 would become Harry, Tom, **** on weeks 2 and so on.

Perhaps I should have epxained this first!

As Stephen spotted no values were assigned to fc and lc they have now and it works.

Mick I will try your code when I next with the programme.

Thank you for the replies.