LinkBack URL
About LinkBacks
Hi,
I want to make a macro that rotates values from a list
So I write in a list of names, and every time I run the macro it prints a new name from the list in a cell. And it shows the names in order.
How would I do this?
Thanks a lot!
André
Hi,
I want to make a macro that rotates values from a list
So I write in a list of names, and every time I run the macro it prints a new name from the list in a cell. And it shows the names in order.
How would I do this?
Thanks a lot!
André
Q1: Where is the list stored. On a Spreadsheet or in Visual Basic
Q2: Which Cell is the Name Written in
Q3: Are the names printed randomly or sequentially.
Q4: "And it shows the names in order" does that mean List the names sequentially.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
The list is in excel
B3:B13 but not all cells will be populated. The first ones will though
The names are sequential.
Yes, so if the list is
John
Cate
Lisa
It first shows John, then deletes John and shows Cate, etc.
Thanks a lot!
Right Click on the sheet name at the bottom of excel and select view code
Paste this code in the Visual Basic module
Close the Visual Basic module
Click on A1.
Click somewhere else
Click on A1.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address <> "$A$1" Then Exit Sub Set Last = Range("B3:B13").Find(Range("A1").Value, LookIn:=xlValues, Lookat:=xlWhole) Pos = Last.Row MyLoop: Pos = Pos + 1 If Pos = 14 Then Pos = 1 If Cells(Pos, 2) = "" Then GoTo MyLoop Range("A1").Value = Cells(Pos, 2).Value End Sub
Hi,
Thanks a lot.
That works once, but I would like to have it so it is linked to a button and only shows the new value when I click on the button.
IS that possible?
It will work every time.
but you need to click on A1, click elsewhere and then back on A1.
It is a selection change macro.
If you want to use a button then this code is all you need:-
Sub Macro1() Set Last = Range("B3:B13").Find(Range("A1").Value, LookIn:=xlValues, Lookat:=xlWhole) Pos = Last.Row MyLoop: Pos = Pos + 1 If Pos = 14 Then Pos = 1 If Cells(Pos, 2) = "" Then GoTo MyLoop Range("A1").Value = Cells(Pos, 2).Value End Sub
Hi I tried to get the sub to work, but first it showed me Error 400 and now it says "Object variable or With block variable not set".
Not sure what is wrong.
Changed the ranges slightly, maybe you know what I need to fix?
Best
André
--
Sub rotate()
Set Last = Range("I5:I15").Find(Range("F3").Value, LookIn:=xlValues, Lookat:=xlWhole)
Pos = Last.Row
MyLoop:
Pos = Pos + 1
If Pos = 14 Then Pos = 1
If Cells(Pos, 2) = "" Then GoTo MyLoop
Range("F3").Value = Cells(Pos, 2).Value
End Sub
Last edited by andre1986; 01-12-2016 at 09:50 AM.
now excel just crashes
Do you have any names in B3 to B13?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks