Excel/Excel 2007

Advertisement


Question
I have a list of names that is randomized by using RAND(), Large...  and VLOOKUP......

When I hit the F9 key the list of names gets randomized without repeats and works just fine. I also have a command button that transfers the list of names after I randomize them to another sheet, however when I click on the button the list gets randomized again and does not display properly in the second sheet.

How can I avoid getting the list of names randomized when I click on the button clicking the button acts in the same way as hitting the F9 key.

Thanks
Barry

Answer
Hi Barry,

I assume when you say it does not display properly in the second sheet you mean that it does not display the same randomization as displayed on the source sheet, but rather the randomization previously calculated.

It is not the button click itself that causes the recalculation.  When the code of your command button transfers the randomized data to the other sheet, the resulting changing of the cells on the other sheet triggers a recalculation of all the worksheets. And of course this recalculation causes your source sheet to re-randomize the data.

There are several solutions to this depending on the functionality you want.  You can just turn off calculation by setting it to manual mode.  This may not give the functionality you want because if you need to go back to automatic calculation mode the source sheet will immediately re-randomize (recalculate).  

If you need to gain control over whether or not the data on the source sheet recalculates, even in automatic calculation mode, I recommend that rather than randomizing your data using formulas in the source sheet you create a button that does your data randomizing via VBA code.  Then you would not need formulas to do it and the randomizing would only occur when you click this button.  If you decide you want to do this but are not sure how, let me know and I'll provide some help.

I hope you find this helpful.

Damon
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Damon Ostrander

Expertise

I have extensive experience with VBA programming in Excel 5 through Excel 2013. As a former aerospace engineer with a large aerospace corporation and consultant in a small defense technology services company, I have developed a wide range of applications in VBA, including simulations involving mixed-language programming, satellite orbit mechanics, graphics and animation, and real-time applications. I am interested in moderate to hard VBA-related questions only.

Experience

I have developed and taught several courses in Excel VBA programming and also VBA programming in Office 97, 2000, and 2007. I have developed a number of large technical applications in Excel VBA for use within the aerospace industry.

Education/Credentials
B.S. in Electrical Engineering and Computer Science, University of California, Berkeley.

©2016 About.com. All rights reserved.