Rotate a list

b9k9uk

New Member
Joined
Jul 31, 2016
Messages
37
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
Perhaps:-
For data in column "A"
Code:
[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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top