Optimize Select Random Cell Value Without Duplicates Code
Apr 6, 2013
I have two worksheets. ws1 contains a large number of dates on column 3. ws3 contains around 20 dates on column 3.
I want to pick two dates randomly in ws1 that does not already exist in ws3.
Here is my working code, but it is really slow to process. How to optimize this code? Also I would like to add a function where if the macro is not able to find two random dates that aren't already in ws3 then exit loop.
VB:
Set ws1 = thisworkbook.sheets(1)
Set ws3 = thisworkbook.sheets(3)
lr = ws2.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Set rvis = ws2.Range("c2:c" & lr).SpecialCells(xlCellTypeVisible)
mynodate = 0
Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;
I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.
Public Sub Farvelade() Dim icolor, Navn As Integer Dim TargetRow, LastRow, Previous As Long Dim Target As Range MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen" Range("A5").Activate Range("A5"). CurrentRegion.Select 'Set range ActiveCell.Offset(Selection.Rows.Count, 0).Activate LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................
I have an excel sheet attached that I would like to customise to become a random seat picker. I wanted it to select a random cell representing a seat and highlight that cell in someway (e.g. change the background colour or text colour) and display the data within in in a cell in the middle of the sheet.
I have used formula to achieve the later half of the problem but am unsure how to highlight the cell. my current solution uses f9 to refresh the data. Ideally I would like to attach the behaviour to a command button. I have attached the excell sheet and have highlighted the problems I am having.
In column AB I have a list of 28 weeks written in date format.
In column B I'd like to chose one week from the 28 weeks listed in AB at random. But B2 - B9 can not have the same date (ie could not have two dates for the 19th of April).
How exactly is that done?
Alternatively - is it possible to have in Column AA the 8 team names. In Column AB the 28 weeks. In Column A a random team picked from column AA and in Column B a random date picked from Column AB. Both column A and Column B can not have duplicates... There's 28 weeks with 8 teams - therefore I only need 8 unique weeks.
How do I keep the same names from coming up on the same column when randomly generated there (I hit f9 and names randomly generate on my sheet)? I'm trying to integrate this fix into =IF(Override!G5>0, Override!G5, Shuffle!A34) formula. That formula is the formula that recieves the randomly generated names into the cells I have chosen. The override is so I can do just that if I dont like the generation of names.
example:
name 1 name 2 name 3 name 1 <---if that happens I want this cell to respond by reshuffling until it equals a name that is not in its column, but just that one cell, not the others.
I need the easiest way to randomize or generate team numbers in a league. Using COL A assign numbers between 1 & 8 without duplicates. Then repeat 5 more times. This has to be done on the spot and has time constraints as the players will be waiting for their team assignments.
EXAMPLE: I have 48 players which will be assigned to 8 teams of 6. I want to randomize the drawing so the same players don't play on the same teams each week. Also to be able to adjust number of teams determined by how many players are present. either 6 , 8, or 10 teams.
I'm needing to generate a unique random value for a database with 3546 cases. The unique random values cannot be duplicates of each other. I tried the =RANDLOTTO function that I learned of in an old post on this board, but that results in "#NAME?" appearing in the first cell. I tried to install the Add-Ins (both the Analysis ToolPak and the Analysis ToolPak - VBA, but nothing seems to happen. Is there another way to generate these numbers?
I have been thinking about a name selection tool that would automatically pick a few names for internal audit. I can handle the useform launch and other basics but here is what I see happening.
1. The empname! sheet contains a list of all the company employees, with employee number in column A, names in column B and an "X" or blank in column C.
2. The main sheet called auditmaster! contains nothing currently
3. When the code is ran the userform prompts the user to select how many individuals to audit by entering an integer in a textbox.
4. If the user selects 5 for instance, the code will run and will select 5 random employees for internal audit. It does this by first checking column A of the empname! sheet and seeing what the highest and lowest numbers are. This would create the range for the random function.
Next if there is an "X" in column C of empname! sheet, those employees numbers are excluded from the random choice.
Following this the function will select the appropriate number of individuals to audit using random again and place their names/number on the masteraudit! sheet.
The detail should be placed as the following, employee number goes to column F, name to column G. Starting with row 10. The code should also number the results 1 - however many were selected for audit in column E.
I need a macro that will select a random number between 1 and 6. ie select the number of a dice. It needs to input 1 random value into one cell and another random value into another. Like the dice values of a monopoly game.
I would like to use the random function on a range of select numbers. I tried randbetween() but it won't work for me since my range of numbers are not in sequence (e.g. 1,2,4,6,7,8,9,10,21). Using randbetween() might result in numbers not within my range..
I'm trying to select an x number of random selections within the selection.
Basically, lets say A1:A100 is selected, im going to have a inputbox asking how many to select, you put 10 in the inputbox, and then it randomly chooses 10 from A1:A100 and highlights them
This seems easy enough, i can do the input box and know the random function. just not sure how to set the beginning point and the ending point with Cell locations and then have it highlight those selections... a for loop I'm guessing?
Say I have a list of 100 numbers (in column A). What formula would I use to have Excel list (in column B) 50 random numbers from the list of 100 or column A?
I've got a list of items (formatted alphanumeric) in column G starting at row 2. I would like a cell formula that would randomly select 30 items from this column and place them in column H starting at row 2. One caveat is that the list must remain static once created. If the Rand()function is used, it recalculates whenever the worksheet is updated so that might be a problem.
Cell formula to do this? A VBA solution would be OK as well.
I am looking at ways to select 81 winners at random from a spreadsheet with 5000 + names on it. I have tried assigning each cell with a value between 1 and 81 and then removing the duplicates but i cant seem to get excel to pick 81 unique numbers, instead it will pick no number 1, or two number 70s.
I have managed to get a macro to pick 1 cell at random but cant get it to pick across the whole range of 5000.
I need the selected rows to be displayed on a new sheet. The idea being that I have a quiz with hundreds of questions and I only want a random selection to appear when I execute the macro. I plan on hiding the original questions and only displaying the randomly selected ones.
1. Randomly selects 50 rows from 834 on "worksheet 1". There are only two cells per row.
2. Then, it clears "worksheet 2" and "worksheet 3".
3. From each selected row in "Worksheet 1", it splits the two cells and copies the results into two different worksheets, one column per sheet.
4. The rows in the two new worksheets should be congruent (i.e. same selected order from worksheet 1). Keeping the order the same is important.
This is what I have so far:
-------------------- Sub Macro1() Dim rng As Range Dim iRow As Long With Rows("1:834") Do iRow = Fix(Rnd() * 834 + 1) If rng Is Nothing Then Set rng = .Rows(iRow) Else Set rng = Union(rng, .Rows(iRow)) End If Loop Until rng.Areas.Count >= 50 End With
I would like to select say 2 id from sheet1 and 2 names from sheet 2 randomly and copy to sheet3, to cells a and b,have seen various codes but none seem to fit the bill.
I would like to know how I can get excel to randomly select one line of data for each of the people listed below (so one for bob, one for jenny, one for Trish etc.). I have been trying a mixture of vlookup and rand but to no avail, below is just a sample list but I would like to try it on a large set of data. I have been able to get a random transaction but not one for each person.
I am trying to copy/paste the same data range from many sheets into a summary sheet.
Specifically, I'm attempting to copy (B697:G710) from 100 or so sheets into the summary sheet, one after another
I don't know much about VBA and am using the macro recorder. Unfortunately, when I attempt to paste the data from the next sheet to the summary sheet, it overwrites the info from the prior sheet. I have attempted using the down arrow and Ctrl downarrow but the recorded macro continues to select the same cell in the summary sheet into which it pastes the data.
I have a row of formulas that reference other sheets in my workbook (i.e. Cell A4=Sheet2!A1, Cell B4=Sheet2!B1, etc). I need to have VBA find the last cell in that row with data. I tried "End(xlToRight).Column" but it goes all the way to the end because all of the cells have formulas. I need to find the last cell that is empty of data or maybe >0 would work.
I have a button set to bring up an inputbox. enter serial number.
What I need to do is enter the serial number and then have the macro look through a single column of values to match it then go to that row.
This seemed so simple I thought I could get it but alas here I sit...
I have tried to search the forum for a similar code but haven't found one that was close enough to make sense to me. I have ut together alot of individual actions, but how to get the value inputed to "search" the column and once found go to that row.
I also need to make sure it accounts for new rows being added in the future. (no set row range)
1) change to a different sheet 2) select a specific cell range 3) save that range as a text / xml file with a filename derived from a cell outside the given range
I have a large database of equipment on one sheet and an input form on another. For inventory control, when a user scans a number into the input form, the main inventory sheet is updated with the current location. I have it working using functions, but I need to do this in VBA. I am looking to do the following;
User enters 2222 on the input form. The code matches 2222 on the inventory sheet, moves the activecell 10 columns and updates a value in that cell. Cell A2343 is "2222" then Cell K2343 is changed to "WAREHOUSE 4". What is the best way to find the match and then select that cell in VBA?
