I need a cell to display the (text) content of 1 of 25 cells. example: I have text content in all cells ranging A1:A25. I want cell B1 to display the content of 1 of these at random. is there a function for this or do I need to use a macro?
My wife is a teacher and she has asked me if there was a way of randomly selecting a child's name from a pre-defind list and for that child not to be selected again during that session.
Up to 35 children in the class (selected from a pre-defind list)A child can only by picked on once during the lessonA method needs to be in place for seelcting the child's name (either a button that can be pressed, or another option).
I'll be collecting registrations on an Excel worksheet at a company event. Users will fill out a registration form, click a button, and the information is moved to a hidden sheet. That part works perfectly.
Periodically during the day, we'll give out door prizes, selecting winners from the list of registrants.
I need a macro that will look at the entries in column A in my hidden sheet and return one of them randomly.
I know there are random number generators, and I could make one of those work, but if I could just have it make a random selection from the populated cells in Col A, that would be slicker.
Question 1) Is there a way that whenever I select Cell B39 that it will copy cells B39:V39 ? From there I can manually paste that selection to where I need to.
If this is possible, it would save the effort of having to select B39:V39 manually and clicking CTRL-C. I am gathering data and the copying and pasting is killing me.
Question 2) Is it possible that when I click on Cell A9 that it can paste any data that I just copied from another worksheet to cells A9:D29? The size of the selection that I would copy is the same size as A9:D29 (4 columns & 29 rows).
I have searched for a solution, but I am not having much luck since I am anaware of the proper terminology for these types of actions.
I have a form that loads and depending on the word selected in the drop down the following code loops through cells Q2:AC2 until it finds the word in one of those cells (the word will always be in one of the cells)
Code: For Each c In Range("Q2:AC2").Cells If c = period Then c.Select
The active cell it finds will always change, i know I need something to code the active cell back but I don't know what it should be.
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)...")
I would like to select a range of cells relative to the current cell and move them to the right two cells. Basically, if I were in cell A1 I want to be able to have a macro select A1 - A8 and move them over by two cells leaving A1 and A2 blank.
I created a multipage spreadsheet to track stocks. Two pages import quotes from Microsoft automatically about every 10 minutes. The main page organizes these stock quotes next to the symbols. Stock price, $ change, then % price change.
For example, for symbol INTC, PRICE cell is =Quotes!D120. $ change is =Quotes!J120 % change is =Quotes!I120
Now all of a sudden, during a random refresh, EXCEL IS CHANGING THE VALUES from D120 to AB120. J120 to AG120. I120 to AH120. That was after fixing it earlier today. Earlier today it randomly changed those values to something else. Like A, T, and V substitutes for the proper fields. This is happening to about 10 stocks in a row, but I think I saw it on a couple others earlier. It is as if someone is changing these fields (but they aren't!) The fields on the pages being pulled from are staying the same.
Using Office (Excel) XP on a Windows 7 PRO 64-bit OS.
The macro below will randomly pick a cell (with names)and fill with color. What I want to add is after it pick a cell a msgbox with a name of that person with YES/NO, if YES fill color. I need it not duplicate once it has already selected that cell(person).
Code: Function RandCell(Rg As Range) As Range Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1) End Function
I am trying to make a macro that when clicked, will randomly choose one of 7 values and type that into cell C4. Values can be Dodgeball, Football, Soccer, Tetherball, Soccer, Baseball, Basketball. Is this doable?
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.
I have got stuck on one piece of my code and having trouble fixing... Overall I am trying to find variable station name in cell L2 of Sheet 2 in Sheet 1 and then select and copy the data from the data in "cell L2 of Sheet 2" to the last entry of that row. I have attached an example test spreadsheet of the data and a macro is within Sheet 1 called test1. Please note that cell L2 in Sheet 2 will always be different station name and the station list in Sheet 1 will change with differing station name.
The code I am using is:
The code that is not working and bringing up an error is:
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
I am receiving a 'subscript out of range' error on the lines of code below.
I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.
Below is a snippet from the immediate window which indicates what the values are: completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls cashsheetname Formatted Sheet cashcurrcolumn A cashfirstrow 2 cashlastrow 876
Also the workbooks are both closed at this point (but it makes no difference)
Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _ Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _ Range((PELCurrColumn & PELlastrow)).Value
After this I would like VBA to copy all the variables in a range that is bounded by startcell and endcell. Here is what my code is currently for selecting the range:
Range("A2:" & endcell).Select
Instead of A2, I would like to select startcell, but I cannot figure out how. Currently I can get VBA to recognise endcell. For example, using the formula above I can copy the range from A2 to endcell. However, when I try and use startcell as the beginning of the range (in place of A2) the code does not work.
I have two tables in two different worksheets. In the first table, rows get deleted if the value in column 'S' (the first row to the right of the table) is not '1', and columns get deleted if the value in row '2' (third row above the table) is not '1'. In the second table, the number of columns stays always the same but rows get deleted if the value in column 'N' (first column to the right of the table) is not '1'.
After the rows and columns are deleted the tables get pasted in a ppt presentation. Now the problem: I am a beginner in VBA and up til now I am only able to define a certain Range, e.g. ("A5:M17"), to be copied and pasted into ppt. But since the tables will sometimes be bigger than other times depending if rows/columns are deleted, I would rather define a dynamic Range. The top left cell of both tabels to be copied is always 'A5' but the bottom right cell changes.
I have a spreadsheet with Employee details and each employee has a Indicator and a Score. I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.
If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times....
I am having a few problems selecting a range. What I am trying to do is select a range where the number of columns stay the same, but the rows are variable; ranging from one row upwards. At the end of the rows, there is a blank row. I currently have;
I am trying to select a range of cells. The range I need to select starts in Cell A1. I need to select every row with data in column 1 up to the first row with no data. I then need to expand the range to all columns up the last column with data, including columns with blank cells.
The problem with my code relates to the selection of columns. It is only selecting up to column F. Column G is empty, Column H has data, Columns I-J are empty, Columns K-R have data.