Randomly Generated Numbes

Aug 30, 2007

I wish to create a new sheet/workbook which promts the user to enter a start number and an end number. I want to then display 20% of randomly picked numbers between the start and end figures.

For example, the user enters 1 as a start and 20 as an end figure. 20% of this is 4, hence I want the system to randomly return 4 numbers between 1 and 20.

View 9 Replies


Randomly Generated Number Focusing On Range

Jul 30, 2012

What I need to accomplish here is to randomly (or at least seemingly enough) generate a number between A and B values for many cells.

I know i can accomplish this through =Rand()*(B-A)+A

However lets say that the lower limit is 96 and the upper is 100. How would i get my data spread to appear to center more around 98-99, with a few randomly placed a little farther out but still within 96-100? I'll say i have 500 instances of this generated number. So what i need is the appearance that most of the numbers lie within the inner range with some anomalies outside that but still within the greater range, having fewer and fewer instances as the value gets closer to the outer limits.

I'm also placing this value in cells using VBA (most likely "for each cell in range(xxx)...")

How would i go about this?

View 2 Replies View Related

Highlight Cell Based On Two Randomly Generated Number

Jan 19, 2014

I am trying to highlight a cell based on two randomly generated number, one is the column and the other is the row. Based on those values I want to change the color of a cell in a range of data. The formula I am using does not work like I intended. It only changes the cell once I enter a value in the cell not until. =OFFSET(A1,B27,B26). B26 and B27 are the cells housing the random numbers and A1 is the base reference.

View 1 Replies View Related

Excel 2010 :: Adding Randomly Generated Value To Range?

Apr 7, 2014

Not sure if there is a way to do this. I'm using excel 2010. I used this function


I have a list on names that is in the range on Sheet5!A1:A149 I'm using the function to randomly select one of those names.

What I want to do: I want to add the randomly generated name to a range that I can then refer to later to see if that name has already come up. So I know if that name has already been used.

View 3 Replies View Related

Concatenate Formula For Numbes (% And $)

Aug 7, 2006

I have been able to create concatenate formulas for numbes (% and $), but have been unable to create one that will display a date, I. E. for the number formula, I used, =(A8&" "&TEXT(B8,"$0")). What would be the formual so the second cell show the value as a date?

View 2 Replies View Related

Email Generated

Jan 18, 2009

Iím creating a spreadsheet to control corrective actions. Each corrective action will have a date that it needs to be completed by. When the completion date plus one day is passed, I need the cell that the completion date is in, to turn a specific colour and a warning mail to be sent via outlook When a further six days have passed I need the cell to turn a different colour again and another email is sent

Ive generated the following code to call an Outlook subroutine if the correct conditions are met. The subroutine runs ok but I can get the sheet code to run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A1"), Target) Is Nothing Then
If IsDate(Target.Value) And Now() = Target.Value + 1 Then
Target.Interior.ColorIndex = 3
Call Mail_with_outlook
ElseIf IsDate(Target.Value) And Now() = Target.Value + 6 Then
Target.Interior.ColorIndex = 5
Call Mail_with_outlook

End If
End If
End Sub

View 9 Replies View Related

Sort Some Data That Is Generated From PDF

Feb 7, 2014

I'm trying to sort some data that is generated from a PDF. When I copy the PDF into Excel all of the data is placed into one cell. Is there a function or formula I can use in VBA or within the spreadsheet to accomplish this?

For example, the cell may contain "Employee John Employee Steve Employee Dave Employee Jed Employee Mark" in cell A1. I want the function to list John, Steve, Dave, Jed and Mark in separate cells. So I need to get rid of the space bar hits, the word employee and to separate the names to different cells.

A1 currently is "Employee John Employee Steve Employee Dave Employee Jed Employee Mark"

I would like to create a formula to have cells:

A2 = John
A3 = Steve
A4 = Dave
A5 = Jed
A6 = Mark

View 3 Replies View Related

Too Many Emails Generated By Code

Jul 11, 2006

I have the following code that will look through an individual worksheet, if the value in column D is equal to 5 and the value in cells E6:L1000 is "a" then it should generate an email. The problem I am having is that it generates 8 emails, one for each column. What did I do wrong in the code? It should only generate one email for the cell which has the "a"

Sub Test()
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim cell As Range
Dim wsWH As Worksheet
Dim lngMax As Long
Dim intFiveDays As Integer
Application. ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
On Error Goto cleanup
Set wsWH = Worksheets("WH")
lngMax = wsWH.Range("A65536").End(xlUp).Row
For intFiveDays = 6 To lngMax...................

View 7 Replies View Related

ASP Generated Spreadsheet. Cannot Copy All

Dec 6, 2006

We are having a sporadic issue throughout our company where when users attempt to open an ASP generated spreadsheet in Internet Explorer and then hit CTRL+A to copy all contents and attempt to paste into a regular Excel-launched spreadsheet (i.e. launching Excel independent of IE), the last two columns are not being copied. Further, when attempting to just copy the two columns, themselves, only the first is copied, and when attempting to copy one cell each in the two columns (that are in the same row), only the first cell is copied. Additionally, when examining the clipboard in this instance, only the first cell is being copied to the clipboard, so for some reason attempting to copy it out of the Excel spreadsheet that opens in IE is when the issue occurs (vs. having the issue occur when you are trying to copy into the new Excel spreadsheet from the clipboard).

View 6 Replies View Related

Dynamically Generated Range

Aug 14, 2007

I am have some code that takes values from a range (sourceAll) defined on my worksheet as:

Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", "S34")

I'd now like to be able for the range to be generated dynamicaly, but my problem is that the far right column (S) does not always have a value for every entry. Is there a way of using a column that does have a value for every entry, say column B, but then extending the range accross to S? If possible I'd like to avaoid having to move one of the columns with values for every entry. Auto Merged Post;I was looking at some of the suggested threads and found some code that I worked into my own

Set countRow = Workbooks(pathnm).Worksheets(m).Range("B1250").End(xlUp)
Set sourceAll = Workbooks(pathnm).Worksheets(m).Range("A1", countRow)
sourceAll = sourceAll.Resize(sourceAll.Rows.Count, sourceAll.Columns.Count + 17)

now this seems to work mostly, but it has deleted everything that was in column B.

View 2 Replies View Related

Randomly Add Name To 5 Out Of 6 Boxes?

Feb 23, 2012

I have made a calendar in excel, and need to make the following work.

I have six boxes (each for one day of the week, excl. Sunday) and a separate box containing names. I need to come up with a way that excel will automatically add those names to any five of the six boxes at random (always leaving exactly one box free for each name), and always choose which box at random.

View 1 Replies View Related

Change Generated WorkSheets Name And Formats?

May 3, 2014

I have a file here that already has macros in it. The file is basically a excel document generator. When you click create sku, the document will generate multiple documents based on the user inputs.

1) In the generated documents the original "generator" file creates, I need generated files to have column B and D formatted to TEXT, currently all the generated files are formatted to general. (This code is in module 1, line 84 col 34.)

2) The other problem is I need to append the value in Cell N1 in the "sku data entry" sheet to be appended to the generated file names. (this part of the code is is in module 1, line 150 col 28.)

I was unable to attach my file to this thread because it was to big. however I copied the module where I think the code is causing problems:

Sub procData()
' Starting point for read/extract process
Cells(4, 6).Select ' Range = F4 (row 4, col 6)
For x = 6 To 52 Step 2 ' col F to col AZ


View 1 Replies View Related

Schedule Report - To Be Generated For Each Individuals

Aug 7, 2008

I have a schedule sheet (on week days what assignment is be to taken in concerned period).

I need a report based on the same. i.e. I need to generate a report for each individual.

I have attached a sample file, in which I explained the requirements.

View 12 Replies View Related

Passing Parameters From A Generated Sheet

Jul 7, 2009

Prior to Excel 2007, the functions provided by the Analysis Tool Pack (ATP) are provided by a separate add-in. You cannot call them using the methods shown above. First, you must load the Analysis Tool Pack - VBA add-in. Note that this is different from the Analysis Tool Pack item. Once this add-in is loaded, go to VBA, open your project, and choose References from the Tools menu. In that dialog, choose atpvbaen.xls in the list of references. Once you have that reference in place in your VBA code, you can call the functions in the ATP as if they were native VBA functions.

View 8 Replies View Related

Comparing Time Value Generated By Two Different Methods

Feb 28, 2014

I have thousand rows of data in the following format:

Despatched Time
Amended Time

20/4/2013 3:45:00

[Code] ..........

'Date' is generated by =IF(A2="", "", TRUNC(A2))
'Time' is generated by =IF(A2="", "", IF(E2 = "", A2 - TRUNC(A2), TIME(LEFT(E2,LEN(E2)-2),RIGHT(E2,2),0)))

And sometimes I may want to change the Time, so I used another column 'Amended Time' to insert a 4-digit value to do so.

I want to arrange them into batches according to the hh:mm, so for the column 'Batch', I used the following formula:
=IF(D2 = "", "", IF(EXACT(D2, D1),MAX(B$1:B1),MAX(B$1:B1)+1))

The problem is, when I changed the time by inserting 4 digits in 'Amended Time', the time value is actually different from those generated by TRUNC(), so even two cells have the same time '03:46', the time value is not exactly the same and so they are arranged into different batches (please refer to the following table).

Despatched Time
Amended Time

20/4/2013 3:45:00

[Code] ..........

View 9 Replies View Related

How To Format Output Of Generated Report

May 4, 2014

I have written the VBA code to color any figure above 15, but it display rows above 15 as red including other rows.

Below is the VBA code

Sub FillCells()Dim lRow As Long, lColumn As Long
'Using Cells property to refer to range
'Loop through rows
For lRow = 1 To 10
'Loop through columns
For lColumn = 1 To 5


Which shows the output as Workbook1.jpg But my query is how to get only rows above 15 with red color

View 8 Replies View Related

Random Numbers Generated Without Repeating

Sep 7, 2006

In my worksheet in column CC in C1:C20 I want Excel to ganerate 20 random numbers between 1 and 80 without repeating the same numbers.

At present I have the following formula entered and it works, but after I click F9 some repeated numbers are generated.


How can I change the formula or replace it?

View 9 Replies View Related

Random Number Generated By % Criteria

Jul 2, 2008

how to use the random number generator to generate unique numbers (1-55) based on the odd percentages in the attached chart.

for example in the chart, chances 1 through 10 are more likely to be generate first rather then the last chance number 55 based on the draft pick odds....

View 12 Replies View Related

Solver With Code-Generated Model

Dec 14, 2009

Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go.
So far so good.

And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.

Now one of the two:.....

View 4 Replies View Related

Fix Random Numbers Generated With RAND

Oct 9, 2007

Is there a way to control the number generation. I would like to be able to run different scenarios with the same set of random numbers, then regenerate the random numbers and do it again. Right now every time I do anything in the spreadsheet, I get a new set of numbers.

View 4 Replies View Related

How To Pick 50 Numbers Randomly

Dec 10, 2013

In sheet1 columnA I have some data from that i want to copy randomly 50 rows to sheet2.

View 14 Replies View Related

Randomly Match 2 Columns With Each Other

Sep 18, 2008

Basically I have 2 columns:
column 1 is numbers 1,2,3
column 2 is letters a,b,c etc

I'd like a macro that randomly matches column 1 and 2

1 c
2 p
3 n

but i'd like it so that the number 5 always returns z

View 3 Replies View Related

Select Fields At Randomly

Apr 11, 2008

I have a workbook with sheet 1 called Support which is basically an input form. The second sheet is called Database.

I want to enter data into the form and via a macro copy the data to the database. I have followed a text book to the letter on how this is to be completed to the letter.

My problem is that when the data is written to the database some of the fields are blank. Using F8 I can step through the VB script which appears to select fields at random.

View 10 Replies View Related

Randomly Choosing Between 4 Numbers

Feb 9, 2007

Is there a function in Excel that would randomly select one of the 4 specified numbers?

View 13 Replies View Related

Randomly Sort Set Of Numbers

May 8, 2012

I have a 1x20 array with the numbers 1 thru 20 inside. I want a piece of vba code to randomly sort them. I will do this in a loop to create different "starting arrays" for an optimization code. I just need to be able to randomize the beginning. How is this done? I cant have duplicates.

View 1 Replies View Related

Randomly Pair Up Players

Nov 19, 2012

I am trying to make code to randomly pair up players. I have found code which makes a button that randomly pairs up players in a list but I want to make it so that ? players are paired with ? other players instead of everyone at the same time so that a person dosn't get paired with more then one player at a time.

E.g. take the first 6 players in a last and pair them with the second 6.

This is the code I found:

Private Sub CommandButton1_Click()
Dim x As Range, RanRng As Range, z As Range, oRes, Ray
Dim i As Integer, j As Integer, real, oSt As Integer, cl As Range
Dim oCol As Integer
Set RanRng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))

[Code] ........

View 1 Replies View Related

Select Names Randomly

Apr 17, 2009

I'm trying to pull 20 random names from a list of them every time I open the spreadsheet, and have them copied onto the clipboard to be pasted wherever. I have some code I found to accomplish this, but I'm not exactly what you would call VBA savvy. Could anyone look at this and see what I'm doing wrong? Or if it is right what I'm doing wrong to get it to work?

Sub GetRandom()
Dim iRows As Integer
Dim iCols As Integer
Dim iBegRow As Integer
Dim iBegCol As Integer
Dim J As Integer
Dim sCells As String

Set TempDO = New DataObject

View 9 Replies View Related

Display Records Randomly

Apr 21, 2007

give me the script (syntax) and place to be written it in a form in ms access 2003.

1. Text box1 should be displayed the value of field1 from table randomly, when I click the command button1.

2. Text box2 should be displayed the value of field 2 of the same record, when I click the command button 2.

View 2 Replies View Related

Join 1st & Last Names Randomly

Apr 22, 2008

This request is for Excel 2003 rather than 2007 (company does not have Vista or 2007 as I do personally). When two columns are used for an employee's 1st Name and 2nd Name, what is the random formula for combining BOTH columns in a random name pull? Since two people may have the same LAST name, only by accessing both column one (typically LAST name) and column 2 (first name) can they be identified if both last names appear in a random pull. I have used the random generator available from Excel Help online but do not know how to modify the parameters to accomplish keeping the first and second names from Column A and Column B together in the pull.

Explaining this will be widely useful for any company (such as trucking) using Excel for employee first and second names in two columns (assume A1 header 'Last Name', A2 header 'First Name'). Such companies need to be able to do a random pull to comply with DOT (Dept of Transportation) CDL re-certification, substance abuse screens, etc. I am just above a NOVICE user (I have a dozen books I try to learn from before asking).

View 4 Replies View Related

How To Look Up Based On Two Values Generated From Drop Down List

May 10, 2009

Ok what I am trying to do is lookup a value in a table (kinda like one below but alot bigger). What I plan on doing is creating a drop down list for the rows that show "A, B, C etc) and then another drop down for (AA, BB, CC etc).

So lets say the 2 drop-down list are set to C and BB
I want to be able to fill another cell with the value -134

I would use If statements but like I said the table would be alot bigger than example. If possible I would rather not use VBA due to work security settings.

a b c

View 3 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved