I have the below code that i am using to generate a random number between a user defined lower limit and upper limit (these limits are set in cells "B3" and "B4" in worksheet "DRAW"), the random number that is generated displays in cell "B7" on worksheet "DRAW".
The code also allows for the draw result to be stored on a second sheet called "DRAW HISTORY", on this sheet the draw result, date and time are recorded (the date and time are stored on sheet "DRAW" in cells "C20" & "C21" and these values are pulled to the result sheet by the macro).
What i would like to happen though is instead of the random number displaying instantly i would like the numbers to cycle for 3 seconds before stopping and the result being recorded. A much better visual effect than an instant BANG here is the winner.
here is the code i am using to generate the random number and record the result:
Sub testRandom() Dim aRow As Long Sheets("DRAW").Range("B7") =
I was looking for some code to randomly generate a number for a prize draw I am doing. I found a great piece on this site, which I have slightly changed to give me what I have below.
It works great except for one thing I cannot work out, I have the code below working on cell "C1", I would like it to work in cell "C5", but I can't seem to work this out.
Sub SlotsNum() Dim w As Long, x As String, y As Long Randomize x = Format(Int((Range("B2") - Range("B1") + 1) * Rnd + Range("B1")), "0000") For y = 1 To 1 For w = 1 To 250 Range("C" & y) = Int(8000 * Rnd) Next w Range("C" & y) = CInt(Mid(x, y, 4)) Next y End Sub
Also, whilst here. i will have one other problem, with it being a prize draw I do not want to draw the same number twice. At the moment I have a small macro that copies the winning name that appears on the main screen (via a vlookup) onto a winners spreadsheet. I am thinking of then creating another macro that replaces the winning name on the master list with an "Already Won" entry, so we can just select again.
I am entering values in columns AV and BD of the attached spreadsheet. Rather than manually editing each value to adopt the decimal place value of the adjacent cell is there a formula that can achieve this before the value is entered? The decimal place values are determined using a VLOOKUP table (column3) on the NES tab.
I've done lots of looking on the forum, and the helpfiles, but just cant seem to format my text box properly! Its a form where the user inputs to the text box. I want to control thatway where they enter numbers e.g 05052008 and it gets converted to 05/05/2008 or 05-05-2008. I've tried a range of things, but am really stumped!
Col A - budget items (description) Col B - budget dollars Col C - actual dollars Row 10 - summation Cols B and C
When the budget is prepared the actual dollars in Col C are equal to the budget dollars for all items. For instance the formula =b2 is placed in cell C2. As time passes the user records actual dollars in Col C for each item by entering the actual amount directly into the cell. Actual dollars do not become known all at the same time, so that Col C will contain a mixture of formulas and entered numbers.
How can I highlight the cells in Col C that have entered numbers rather than formulas.
The following code works perfect but the "change" event is only triggered when working directly on intersect range. Tried using the "calculation" event but could not figure it out. This is what I want:
1) To replace the code provided below using the calculation event 2) To only trigger the event for the row(s) where the new value was generated, not for the whole "For Each" statement 3) To use one single code for all worksheets, instead of copying the code in every working worksheet on the workbook, if feasible 4) And I would like a "second alternative", where the user of the workbook can click on a button and trigger the event on every row on the workbook that has a non empty cell within the intersect range, assuming that the intersect range column is the same for all worksheets
Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, d As Range, fc As Long, bc As Long, bf As Boolean Set d = Intersect(Range("I:I"), Target) If d Is Nothing Then Exit Sub For Each c In d Select Case UCase(c) Case "C" fc = 1: fb = True: bc = 4 Case "O" fc = 2: fb = True: bc = 3 Case "D" fc = 2: fb = True: bc = 46 Case "G" fc = 2: fb = True: bc = 5..................
I have 1200 doctor/patient records to input into an excel spreadsheet for import to an online EHR database. I can set up all the normal formulas and formatting but for the life of me not figure out how to create a custom formula to take the first letter of the patient first name and last name and add 6 figures to create a unique patient identifier.
ie. James + Smith+ random 6 figures = JS245318.
In my spreadsheet the first name is under Column 1, Last name Column 3 and the unique number generated in column 4.
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
I am working on a Random Cycle Count Generator that provides random SKU#s based on 3 separate columns of SKU listings. The user clicks a button to generate the SKU#s to cycle count for that day. What I would like to see is a date stamp in the columns next(B,D,F) to the referenced SKU listing(A,C,E) based on which SKU#s are generated. This will let me see the last date that the SKU was generated. I would also like it to automatically save after generating.
I have a column of numbers and want to make sure everything has been entered correctly from our scanning software. Basically, I want to automatically highlight any cell that has any letter in it (e.g. z12o2 instead of 21202 or R705 instead of 5705), ignoring any cells that contain only numbers. I haven't had any luck using conditions based on formulas like =ISTEXT.
I would like to draw the border for the cells with in the usedrange from column A to BM.
I have the below code, that I was using to border the column D alone. When i change the range from "A:BM", i dont get the intended output. Can somebody tell, what modification, I should do to get the desired output?
I cannot change the number format in a single sheet of my spreadsheet. I can go to format cells and select colour etc but it will not change the number format.
Another issue on the same sheet is that linked cells i.e cell b3 formula "=A3" displays "=A3" not the actual value in A3. Again, i cannot change the number format of this cell and yes, auto calc is on.
Whilst not wanting to offer a partial explaination (per the rules) just before this happened i was pressing a combitation of Shift+Control+` - not sure if that is relevant.
I want to get the sheet back to its 'normal' working order.
I have a number in a cell of 1025132.36. I need to scale it down to this number with this formatting: 1,025.13 (Two decimal places with comma separaters)
Basically it is dividing it by 1000 or scaling it by 3 (move the decimal place to the left three places). I cannot use /1000 in the cell and I cannot reference it to/from another cell. I must use something under Format/Cell. I was trying to create a custom format.
I saw in another forum thread to use #”.”###. This does the scaling but then I can’t make the number viewed with only two decimal places. Also when I tried to add comma separators for thousands it put a comma right before the decimal.
If Cell A1 in my spreadsheet is 1, then cell A2 should appear formatted as a percentage. So if a user enters 9000 into cell A2, it should appear as 9000%. If Cell A1 in my spreadsheet is 2, then cell A2 should appear as a comma-formatted whole number. So if a user enters 9000 into cell A2, it should appear as 9,000. Conditional formatting doesn't appear to let me do this.
Cell L90 to L120 are input cells where I type " Percentage" or "Dollar Amount". I need the five cells to the right of each input cell starting two cells over, to be formatted accordingly. I read a thread similar to what I'm asking for suggesting a worksheet change macro, but I can't quite figure it out for a range of cells. Also, in case I need to add a row to my sheet before all of these cells, how can I make sure that this will still work without having to go into the code. Is this an activecell thing?
I have a SS of a WW1 casualty list and one of the columns is Dates of Birth but the data has been entered in three different formats eg 21/2/1898 or 21.2.1898 or 21 Feb 1898. I have tried to Format Cells and change the date format to the slash but after highlighting a number of fields and right clicking they will not re format. Though i use Excel quite a bit i am a general user and dont understand it in depth.
I would like to ask if there's a way to generate a random 4 characters in one cell and the specific random 4 characters that has been generated will be copied until the last cell of my preferred choice?
The format that I want is that:
yymmdd / random 4 characters that has been generated / 4 numbers that will increment sequentially
For example: in A1: 140330QWER0001 in A2: 140330QWER0002 in A3: 140330QWER0003 . . . . .
But here's the catch, the file that I am using when closed then opened again will generate another random 4 characters with the same format. If i opened this file tomorrow:
for example the date for tomorrow is March 31, 2014, the file will do:
in A1: 140331TYUI0001 in A2: 140331TYUI0002 in A3: 140331TYUI0003 . . . . .
I tried to record it, the Rand() function when copied will generate another 4 characters.
I would like to be able to enter a number in cell a1. Let's say 44.2. I want that number to actually be locked in as 100'-0" in cell b1. Below in the next row I want to enter another number, lets say that one is 41.5 into cell a2. And then I want the cell b2 to return the difference in feet and inches. So ultimately the only cells that I will entering data into is in column a and column b would just return information.
The other thing I am trying to figure out is a formula for converting decimal feet to feet and inches (which I have found) but a formula that is less than 40 characters.
Im trying to enter an employees name into a data list. I have a program that generates a list including their employee number into column B. I want to write something to put the employee name into column C. Im working with 60+ employees so i dont think if statements will work? anyway to reference a chart or table with the numbers and names on it?
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.