# Creating CheckBoxes With VBA Results In Random Order

Jan 7, 2010

I have some VBA (using Excel 2007) that deletes and re-creates a bunch (20-ish) checkboxes in a spreadsheet. Since i'm using the controls-based checkboxes, Excel defaults them to object names of "CheckBox1, CheckBox2, CheckBox3, etc" as I create them the first time around.

Later on, i clear all checkboxes and implement the SAME checkbox-creating code to re-create all or some of the checkboxes, but Excel doesn't number them in order the second time around! I end up with something like "CheckBox2, CheckBox19, CheckBox3, CheckBox12, etc."

What could be causing my code to create things out of order the second time around? Is there a way to reset Excel's Checkbox-numbering iterator?

The code i'm using to create checkboxes is:

Dim x As Long
Dim BoxCell As String

BoxCell = ""
x = 0
'j gets passed into the sub as a "Long" somewhere between the values of 5 and 35

## Creating Checkboxes Dynamically On A Form

Oct 31, 2012

I'm using below code for creating check-boxes dynamically on a form:

Code:

Do
Set cb = Me.Controls.Add("forms.checkbox.1", "Cb_" & option_counter, True)
With cb
.Left = 100
.Top = 100 + height_diff
.Caption = Sheet1.Cells(quest_num, option_counter + 1).Value
End With
height_diff = height_diff + 30
option_counter = option_counter + 1
Loop Until option_counter = total_options + 1

It is working fine.....Now after this, I want to check which are the chechboxes whose value is true. How do i do that?

## Identifying Consecutive Numbers In Random Order

Jun 9, 2009

formula to identify consecutive numbers in order, but having trouble figuring out how to identify consecutive numbers in random order.

Cell M1,N1,O1,P1, and Q1 each have a number, 1,4,9,3 and 7.

We have 3 and 4 being consecutive number but they are not in order, would like help in a formula to put a 1 on an empty cell S indicating that there is a consecutive number with a 1 if there are no consecutive numbers then it would give a 0.

The current formula only works if the consecutive numbers are in order, 1-2, 3-4, 5-6, etc...

=IF(SUMPRODUCT(--(N1:Q1-M1:P1=1)),1,0)

## Locate First Occurrence Of Value In A Column Whose Values Are In Random Order

Dec 12, 2012

This is a re-submission of a question previously submitted because the title for the first submission was so poorly worded.

I have a column that has numerical values in random order. I want to locate the first occurance of a value in that column.

I have unsuccessfully tried an Index-Match function - apparently unsuccessful because the values must be in ascending or descending order?

## Another Random Results Query

Sep 6, 2007

I have a speadsheet containing many rows and columns of customer orders, each row being an individual order. Column A is a unique numeric order identifier (the numbers are not ordered and also not in sequence - for example, the first 5 rows order numbers will be 6, 18, 3, 194, 52), Column B is the customers name, Column C is the address, etc etc etc.

I would like to create a 2nd worksheet which starts by taking a 20% random sample of the data in ColumnA in the first worksheet, and placing it in Column A of the second worksheet.

## Results In Alphabetical Order

Jul 8, 2014

Example Data...

----A---B---C
1---B---1---
2---A---2---
3---E---5---
4---C---4---
5---D---6---
6---G---3---
7---F---6---

I have this formula in cell C1, filled down to C7:

=IFERROR(INDEX(\$A\$1:\$A\$7,SMALL(IF(\$B\$1:\$B\$7>=5,ROW(\$A\$1:\$A\$7)-ROW(\$A\$1)+1),ROWS(C\$1:C1))),"")

This creates a list of cells from column A where the correspsonding value in Column B is greater than or equal to 5.

RESULTS:

----A---B---C
1---B---1---E
2---A---2---D
3---E---5---F
4---C---4---
5---D---6---
6---G---3---
7---F---6---

Any way that would generate these cells in alphabetical order making it look like this:

----A---B---C
1---B---1---D
2---A---2---E
3---E---5---F
4---C---4---
5---D---6---
6---G---3---
7---F---6---

Jason.CellsThatMeetConditions.xlsx

## Creating A Random Serial #

Apr 19, 2007

I have a mastersheet that once I open I would like to generate a 3 digit serial number in cell C22. Once this file is opened and the number is generated I do not want the number to change at that point. I have a macro to save the file with data in certain cells. Once this macro is run and the file is saved I will need to be able to open that file to view info but the serial number can't change.

I would like to do this without an add-in so that any client can use file no matter there settings if possible

## Creating Random Numbers

Jun 2, 2006

Im an using excel to run a simulation of demand over a period of 200 days. i have an average daily demand of 7.75 and a stdev of 2.6. Is there any way to generate random numbers (for daily demand) based on this data?

## Order Results Of Table Extraction

Jun 12, 2007

I am finding that I am often using Max(if(... formulas to pull in Staff ID's that have seen activity in a large data dump. I then use a VLOOKUP to return name based on ID.

Trouble is, the names end up out of order. I want to be able to order them in one of two ways - either by work done, or alphabetically.

## Creating Random Multiplication Table

Aug 9, 2013

Here's what I'd like to do:

Have 10 columns... one column would be for the "1s", another for the "2s", another for the "3s", etc...

But I don't want to have them in straight order (i.e. 1 x 1 =, 1 x 2 =, 1 x 3 =, etc...) because the pattern is easily memorized, no matter what number he's working on.

I'd like to be able to hit a button (or something) and have each column shuffle (or randomize) the order in which the equations will be displayed.

So, a column might come up 3 x 6 =, 3 x 9 =, 3 x 1 =, 3 x 4 =, etc...

And next time it might come up 3 x 5 =, 3 x 2 =, 3 x 4 =, etc...

This way he won't be able to memorize any patterns and he'll actually need to learn his multiplication tables.

## Random Generator For Creating A Test

Jul 27, 2014

I am looking for a product that uses excel to create a random test form a bank of questions. I have 50 questions in all and I need a random test of 35.

I can and have messed around with the following. Excel 2012
A
B
C
D

1
Random #
Test Number
Small-Sort
Random Question

I could even shorten it a bit by placing like this. MATCH(SMALL(\$A\$2:\$A\$4,B2),\$A\$2:\$A\$4,0). Of course this is only the start and it would have in column B 1-50 and in column C&D 1-35 for the selection process. Then I was going to set up on sheet 2 my list of questions 1-50 and my idea was to use 1.1, 1.2, 1.3, & 1.4 for the answers to question 1 and then vlookup. so that when and if question 1 is part of the 35 random then I could pull the answers over as well. but as I started the 'animal' will get bigger and bigger fast.

## How To Generate Random Numbers In Range 1:20 With Unique Results

Mar 22, 2014

In an earlier life I was tasked with finding a "random" method of selecting two numbers from a "1 to 20" range so that the generated numbers can be applied to an set of people who will be partnered in a golf game draw.

It is only one draw per year so I don't care if the players have previously played together in past years.

easily be modified by a "passable knowledge level" person to be able to select a mystery "9" out of 18 holes that count for scores that particular round.

(btw: this is an issue only for the 20 guys who go away once a year to play golf, the world will not collapse if I have to draw numbers out of a hat, just looking for a slightly more elegant solution and I already have a few scoring macros so my first guess (but not only possibility) is VBA)

## VLookup Random Numbers - Sort Range In Descending Order And Return Relevant Value From Column

Aug 27, 2012

I have a column (B) of randomly generated numbers 1-14, and am using this formula range to sort in descending order and return the relevant value from column (c).

=VLOOKUP(LARGE(B3:B20,1),B3:C20,2,) to =VLOOKUP(LARGE(B3:B20,14),B3:C20,2,)

Works great, except when a number is duplicated, (E.G. 14,13,13,12,11,10,10,9,8 etc). It then returns the first value from(C) repeated, and not the value from the second and subsequent duplicated reference numbers.

## Changing Order Of Text Based On Results?

Oct 7, 2013

I've attached a spreadsheet. I want the order of the sentence to change based on the numbers and whether they are positive or negative. Below are more details and the spreadsheet has numbers.

"Total expense for September was 8 higher (lower) than prior year, driven by a higher (lower) claims expense and admin expense"

This might be hard to explain: For the second part (starting from "driven by", I would like the variable that explains more of the positive or negative variance (in the example, claims) to come first.

Here is a different example

Total expense for September was 6 lower than prior year, driven by a lower admin expense (claims is not included because it is higher)

## Show Report Results In Chronological Order

Apr 17, 2008

I'm currently working on a model that calculates the costs associated with various phases of a project. I want to allow the user to input the number of weeks that each phase requires as part of the calculations. The result will then be displayed horizontally in chronological order with the values associated with each phase underneath ex:

phase 1 phase 1 phase 1 phase 2 phase 2 phase 3 phase 3 phase 3

Through multiple lookups I've been able to get the data into this format:

phase 1 phase 1 phase 1
phase 2 phase 2
phase 3 phase 3 phase 3

What can I do to merge these series into one series that is in the order I desire (each phase will vary in length based on the user's input)?

## Creating Random Portfolios From List Of Assets

Nov 11, 2013

I am creating 100 random portfolios from a list of 10 assets. I have the return history of the 10 assets and I'm able to calculate the mean and standard deviation of each asset as well as the covariance/correlation between each of the 10 assets. I want to choose to portfolio with the least variance as well the portfolio with the lowest and highest return.

Would it also be possible to introduce a limit that constrains the maximum allocation to each asset at 25%?

## Creating New Worksheet From Predetermined Random Rows?

Mar 23, 2012

I have 800 rows of patient information, I need to sample 70 patients. I used the Random.org randomizer to get my 70 patients. That randomizer only gives me the line numbers such as 1, 8, 23, 45, 70, etc. I then need to go to Excel and just pick out the patients on those lines I was given above. Is there an easy way in Excel to have my spreadsheet of the 800 rows and create a (simple) calculation that will pick out the line numbers that I need all at once, so I do not have to go thru manually and choose the data from row 1, 8, 23, 45 etc to make my new spreadsheet with only my sample patients on it. OR is there a easy way to pick 70 random lines from a spreasheet..

## Creating Random Weekly Work Rota

Aug 4, 2006

What i would like to do is, create a random weekly work rota, so I just have to go in every week and let excel randomly but fairly creat a rota the employees so that they don't argue about whose doing what and just get on with the work. : D

## Creating Sales Order With Dropdowns?

Apr 22, 2014

I have started to create a sales order. I have three columns that I will be working with. I have three lists in a separate worksheet (worksheet 2) in the same workbook. Column A has part numbers. Column B has descriptions of the part numbers in Column A. Column C has prices for the parts in Column A.

I have created a drop down list in Worksheet 1 from the list in Column A in worksheet 2. What I want to do is somehow link column B and C to Column A so that when an item is chosen from the drop down list, the information will pull through.

## Excel 2010 :: Creating Random Trending Data

Aug 11, 2014

Im looking to create random data for an athlete over about 4 months with daily tests, however when im using the =randbetween(*,*) function the data is too 'Random'. so im looking to create a trend in the data, like real athletes will have. is there a function in Excel 2010 that can do this?

The boundaries of the data are -336,416 (i know its quite a big range)

## Creating Formula To Rank Certain Cells In Reverse Order

Aug 23, 2012

creating a formula to rank certain cells in reverse order.

Example

Cell Total Rank
B1 = 2456 3
B15 = 2369 2
B30 = 2547 5
B45 = 2141 1
B60 = 2471 4

Rank every 15 cells basically in reverse order.

## Excel 2010 :: Creating Multiple / Repeatable Lists Of Random Numbers

May 19, 2014

I am trying to create challenge/response cards. Each card will have a 5-digit number in the upper-left and lower-right corner along with a series of rows containing a line number and 3 pairs of challenge/response words.

Rather than have have copies of these cards, I want the central office to have a macro-enabled workbook wherein they can enter the two numbers from the card and have the challenge/response matrix regenerate on a worksheet.

So, essentially, I want the challenges random number generator seeded from one of the 5-digit numbers and the responses random number generator seeded from the other 5-digit number.

I am trying to use

Code:
Randomize(Seed)
where Seed is one of the 5-digit numbers.

The problem appears to be that, no matter what I use for Seed, the RND(-1) function is not resulting in different numbers.

I am using Excel 2007 and Excel 2010.

## Creating Spaces In Formula Results

Jul 31, 2014

I am using a vlookup formula to link 3 pieces of data together. I would like to insert a space between the 3 pieces. Currently formula result is:

item1item2item3

I would like it to view as:

item1 item2 item3

not sure how to alter the formula to create the space....

## Excel 2010 :: Converting From SUMIFS To SUMPRODUCT In Order To Make View Filtered Results

Jan 2, 2014

I'm using Excel 2010. I have a spread sheet with sales data covering 3 years and multiple customers. I was able to create formulas such as this to calculate the figures for the entire sheet (all customers) by year.

=SUMIFS(J2:J12904,E2:E12904,">12/31/11",E2:E12904,"<1/1/13").

I believe that in order to use filters that show this date for a specific customer I need to convert this to a SUMPRODUCT formula, I've tried this multiple times and had no luck.

## Creating A Purchase Order Form That Will Reduce Time Spent Adding?

Apr 18, 2013

I'm creating a Purchase Order Form that will reduce time spent adding in contact details.What I'm looking to achieve is a form that will be printed with a few formulas allowing sections of the form to be filled out automatically once a singular company name (chosen from a drop-down list I created, currently with a 'Combo box ActiveX') then the rest of the form is filled out accordingly.

At the moment it's a bit of a mess, not too sure where I'm meant to put the ranges.There are currently 2 sheets - Sheet 1 with the form, Sheet 2 with all contact information.

## Creating Multiple Results From Volatile Functions

Aug 26, 2012

I have a model that does a projection for 36 months ahead on a spreadsheet. The projection depends upon a randomising "volatile" function. When I press recalculation a new projection is generated. I want to capture the set of numbers that appears at month 36 through repeated recalculations written to a separate sheet. I want around 500 of these numbers.

I have no experience with macros so doing it with preset functions would be preferable. If it has to be a macro, the easier the better. If invoking the spreadsheet and outputting to a flat file is easiest that is fine too.

## Creating A Table From Various Results Of Data Validation

Nov 17, 2006

See the attached:

1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.

Company Name, Year, Manufacturing, Non-Manufacturing

This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.

## Excel 2007 :: Creating Order Form - Copy Entire Row If One Cell Has Data

Dec 3, 2012

I'm trying to create a very simple order form. It's been a while since I've done this in Excel, and I couldn't find an answer when I searched. (I may be a lousy searcher, though.)

I want the user to enter a quantity in Column E of the "Common Items" worksheet, and have all the rows with quantities copied to the "Order" worksheet. (On the "Order" copy below, I just did a copy/paste to show the desired effect.)

Excel 2007
A
B
C
D
E
F

1
Item Number
Description
Unit
Price
Qty
Total

2
BX-2B1324X
0.9% NaCL 1000ml Bags
CS
\$23.52
5
\$117.60

[Code] ....

I'm pretty sure this can be accomplished with an If/Then, but I'm lost! Optimally, they'd enter their quantities, click on the Order sheet and hit print.

## Creating Chart To Represent Results On ANOVA Analysis

Oct 12, 2012

I am doing an ANOVA analysis of survey results for a set of products, and would like to present a chart to graphically represent the results.

## Creating Multiple Results From Two Text Columns That Have Repetitive Info

Dec 3, 2012

I'm not sure what I'm trying to do is even possible, but figured this is the place to ask the question. I'm trying to compare a list of companies (column a) to a list of employee email addresses (column b) and post results (column c) that display all associated email addresses that are unique to company name. Since multiple employee email addresses can be associated with numerous company names.