I am using the data analysis add-in to generate a random number (using the random number generation) using a normal distribution. The problem is, i want it to recalculate these numbers every time the worksheet is recalculated but the numbers seem to be values only.
View 2 RepliesWith the assistance of SHG, Parsnip, Dave & Daddylonglegs I have manage to put together a little app that assist our staff with scheduling dates in a calendar to avoid a specific trend.
Code provided by SHG has worked perfectly; see below:
Public DaysOff As Range
Function datNext(datLast As Date, DaysOff As Range) As Date
Dim datBeg As Date, datEnd As Date
Dim iLastPd As Integer
Dim iDay As Long, nDay As Long
Dim iYr As Integer, iMo As Integer
Though the built in DAY function on the calendar is affecting the dates that are calculated cos the function PD also references this. Whenever I change the year or month on the calendar; it changes the UDF, which is not supposed to be. If the recommended dates are going change all the time; as a result of this, it makes this app useless.
How do I get around this? Also is this what is causing the UDF to recalculate?
I have two Columns
AC Tm%T%
DOMEQ25%26.5000%
INTEQ17%18.0200%
DOMPR10%10.6000%
INTPR 0%0.0000%
ALTIN 0%0.0000%
DOMFI29%30.7400%
INTFI 6%0.0000%
DOMFR10%10.6000%
CASH 3%3.1800%
Total 100.00%99.64%
The Tm column is the original model percentages for each class. Class INTFI is to be excluded (6%) so I need to adjust each of the other % accordingly and still add up to 100. I thought you simply increase all other % by the excluded amount but I end up with 99.64.
The equation I am using fot T is =C15+($C$21*C15) where C21 is the 6% cell in Tm.
It is working, but the problem It doesn't change the color number automatically after I change the color. There is any way to set up that option so can change automatically?
Function CellColor(rCell As Range, Optional ColorName As Boolean)
Dim strColor As String, iIndexNum As Integer
Select Case rCell.Interior.ColorIndex
Case 1
strColor = "Black"
iIndexNum = 1
Case 53
strColor = "Brown"
iIndexNum = 53
Case 52
strColor = "Olive Green"
iIndexNum = 52
Case 51
strColor = "Dark Green"
iIndexNum = 51...................
I have trouble using the =Concatenate() or =(A1&B1) to combine data in one cell. It will work for the first cell but then when I drag the equation it gives me the exact same result as the first cell. But when I click to look at the formula it looks good and when I hit Enter the cell reads the correct value. So I have to open each individual cell and hit enter. What is goin on here. I have attached a small example.
Also, sometimes I will type in a formula and hit enter and it just displays the formula. It does not return a value.
I have a workbook in xl2002 that has a data sheet with approx 50 columns and 500 rows which is populated from a querytable querying another workbook. There are no formulas on this data sheet.
I have another 5 sheets, only one of which actually has formulas on it and these refer to the Data sheet above (some fairly beefy Sumproduct formulas in the main, but only 1056 in total (only about 650 of these are Sumproducts - the remainder are simple = A1 type or Match formulas with the third argument as True (ie quite efficient)).
There are also 3 dynamic named ranges in the workbook, which are utilised by the Sumproducts/matches. An example Sumproduc is as follows:....
I have been asked to look at moving a very large set of pricing sheets from Excel 2000 to Excel 2003. In the progress of this I have found that the functionality for a UDF in a cell to change/recalculate any other cells has been removed in Excel XP, whereas it was available in Excel 2000. The amount of work required to refactor the code is very large, and before starting on it it would be good to find out if there is a workaround.
The Sheet works in the following way:-User enters values, clicks a button to run a Macro,-Macro calculates all the ranges required to return a price-In many of the cells it is calculating are Functions which go and look at a large number of cells to calculate the price, sometimes writing to other Cells and sometimes having to recalculate other cells-As it is such a large sheet we can't simply calculate all cells before we tell it to price, as they are not all needed and so aren't calculated unless needed for performance reasons.It would be a lot of work to move the functionality from the Functions to the initial macro call, though this would solve the problem. Is there any other workaround?Macro security is set to low.The Errors I receive are the following:The UDF will hit a line like Range("DataRange").Calculate and this will raise a "Calculate method of Range class Failed" error. If it hits a line like Range("DataRange").Value = 1 this will raise a "Application-defined or object-defined error"If it hits a line like ActiveWorkbook.Names.Add Name:="This_Name", RefersToR1C1:="=Sheet1!R8C2" then it also raise a "Application-defined or object-defined error".Any of these run from a UDF in Excel 2000 works.I have a sample workbook if this isn't clear
How do I make a row of 5 random numbers in A2:A6 that are not the same as each other. It's basically for a Bingo card. I have a formula that can calculate the random numbers =INT(RAND()*15)+1 under the B's for example, but I can't seem to figure out how to make it so they are not the same.
How can I make sure that I can generate random numbers that don't repeat using the rnd function in VBA.
View 14 Replies View RelatedI have build a system that needs a lot of random numbers generated to work.
I have used what I thought would work to generate me these numbers like this example:
VB:
Lastrow = ThisWorkbook.Sheets("RPlay").Range("A" & Rows.Count).End(xlUp).Row
RPlayerNum = Int((Lastrow - 2 + 1) * Rnd + 2)
This would generate a random number into RPlayerNum variable, based on the list in sheet RPlay, missing off row 1 as the header.
However we have noticed a massive flaw in this code.... It works as long as the excel file is open, each time run it gives a different random number. However if you close the excel file and open it, then run it, it ALWAYS gives the same number first run, every time.
How to generate random 50 number with 10 group..
View 6 Replies View RelatedIs it possible to use the =randbetween() function to generate a random number between 0.8 and 1.2.
View 4 Replies View RelatedIf I generate a random number in cell A1 ranging from 1 to 3. Can I count how many times the result is equal to 2 if I was to run it 100 times and then show the results in cell A3.
View 9 Replies View RelatedI have a formula to generate a random number between 0 and 10.
View 10 Replies View RelatedI 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.
View 9 Replies View Relatedis if there is a way to generate random numbers between 0001 and 9999. I always need to always have 4 digits. The first couple of numbers can be 0 so that I can maximize the number of variations.
random number selection...
i have a list of 200 items (rows) and i would like to "randomly" select 30 of them...(next month i will get another 200...)
I get the following error whenever I try running a Monte Carlo simulation with iterations in excess of 64000 (The MC simulation calls the 'NormSInv' worksheet function at least once per iteration, and the argument to the 'NormSInv' function is always a standard normal RANDOM number also generated by Excel VBA): Run-time error '1004': Unable to get the NormSInv property of the WorksheetFunction class
Why do I inevitably encounter this error ONLY when the number of iterations exceeds 64000, for instance? Simulations with iterations < 64000 run smoothly without a glitch.
i have worked in access using vba procedures. i can easily create a form in access that generates a random integer number between 0 and 20 and the user will guess the number between 1 to 20 if the random number is 16 and the user enters 14 the program will display a msg box saying too low or too high. here is the code .
Option Explicit
Private Const MAX = 20
Private Const MIN = 1
Private iKey As Integer
Private lTries As Long
Private Function Random() As Integer
Random = CInt(Int((MAX - MIN + 1) * Rnd() + MIN))
End Function
Private Sub cmdGuess_Click()
Dim lGuess As Long
now i have to do the same thing in excel create a program that will ask the user to enter numbers between 0 to 20 and see if the random number and user guess is same or not. but i have never worked in excel using vba procedures how can i achieve the same thing in excel using a vba procedures.
I have a random number generater but it creates a new random number whenever I recalculate the sheet or enter something in another cell. I would like it to choose a random number once and not do it again until a condition, specificaly a date, is met.
View 6 Replies View Relatedhow to put time delay on my program?
like it will generate again every 9 minute?
Private Sub CommandButton1_Click()
Dim x As String, i As Byte
Randomize
x = Format$(Int(Rnd * 1899) + 1, "0000")
For i = 1 To Len(x)
Me.Controls("TextBox" & i).Value = Mid$(x, i, 1)
Next
End Sub
is there any rule for random number to be picked up? Any Rule, Any Formulae, Any Calculation Anything, Any authentic routine which may guess what would be the next random number? for example if first random number is 825587 then what would be the next?
View 4 Replies View RelatedI have the following formulae to pick up a random number from 0 to 9999.
=TEXT(RANDBETWEEN(0,9999),"0000")
I want to start this random search from a specified number. for example if i press F9 it should show 3456 at first press and then onward.
I understand how RAND and RANDBETWEEN work, but have a slightly more complicated random number generation problem that requires a number that isn't solely between two numbers.
I have one cell that is randomly generating a number between 0 and 350 (we'll call this value A) and another randomly generating a number between 0 and 15 (value B). Then, I have a third cell (value C) that equals 15 minus B. Where I am having trouble is with a fourth cell (value D). This cell I want to have randomly generate a number between 0 and 350 but it needs to be D less than the value in A or C more than the value in A. Is there an advanced random number generation technique that can solve this?
I have a spreadsheet of 1000+ companies and I want to take a random sampling of 100 of them. I would assume this could be accomplished by auto-filling a numerical value to each line of data (which I did), then scrambling only the column with the numbers (which I can't figure out), and then selecting the entire text and resorting it to put the numbers back in order and then just work with the first 100 rows (which I can do, if I knew how to make the numbers random).
I have taken on a project that is would seem is far out of my league right now, but need to deliver.
I have attached the example spreadsheet what I am trying to achieve, which is basically a random automated number allocator.
Look forward to receiving your replies,
Example.xlsx
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?
View 6 Replies View Relatedi have the formula =CHAR(INT(RAND()*25)+65) which creates random number when dragged from say C1 TO C6 however i need the 3rd 4th or 5th character to be a random number between one and 9 is this possible?
View 9 Replies View RelatedI have a range of cells that have values from 1 to 7 in them. What I would like to do is to have the next column generate a random number from 1 to 7 that does not generate the number that the cell next to it has. for example if cell A1 has 6 in it, I would like cell b1 to generate a random number other than 6 in it. if cell A2 has 3 in it then cell B2 would generate a number other that 3 (1,2,4,5,6, and 7).
View 9 Replies View RelatedThe assignment is basically to do a module that first picks a random number between 1-100 and then the user should guess what number it is. If the guessed number is below/higher the real number then a MessageBox should appear and say if its too low or to high.
View 8 Replies View Related