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?
View 9 RepliesI am trying to assign a list of players onto teams but I need to distribute "skill" evenly to ensure teams are equally matched.
A statistician in the league suggested the best way to do this might be to sort the player list (descending) by the measure of "skill", then start assigning teams in blocks. If I have 40 players in the league / 4 teams = 10 players per team. I would randomize the numbers 1-4 (e.g. 3 1 4 2) and assign them to the first four players, randomize another set of 1-4, assign them to the next, and so on...
I could then sort the player list by “team assignment” column and have hypothetically equal skill on every team.
Couple of complications... number of players and teams might vary season to season. I will have to enter in number of teams and number of players at the beginning of each season along with a new list of their stats. Also, although I have a limited knowledge of Excel and VBA, none of the other coaches do so I want to make this as idiot proof as possible.
Using various macros (or functions) I found here I was able to randomize the first "block" of players but I cannot complete the randomizations for the remainder of the list. The only solution I have found would be to manually create an array using the “RandUnique” function over X (depending on number of teams) cells and then copy and paste this randomized subset down the rest of the player list.
See attached for sample data.
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.
I'm attempting to assign 1 of 4 predetermined values to a cell based on the values of entries in another cell.
View the example attached : Value assignmts.xls
Assign the Value of 1, to (D8) when any value entered in (C9) is equal to or over 380, but less than 410.
Assign the Value of 2, to (D8) when any value entered in (C9) is equal to or over 410, but less than 440.
Assign the Value of 3, to (D8) when any value entered in (C9) is equal to or over 440, but less than 470.
Assign the Value of 0, to (D8) when any value entered in (C9) less than 380.
In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists).
Range S28:S46 will assume the name of sheet2A11 & sheet2A3.
(example name period_1unit_1)
Range U28:U46 will assume the name of sheet2A11 & sheet2A4.
Range W28:W46 will assume the name of sheet2A11 & sheet2A5
Right now I am calling the code when something is entered into A11.
I have tried if statement and select case, but I ran into complications with both.
I have posted both codes with the questions I have concerning those codes.
For example, I have the numerical value "400" in R1C2 and that value would be used in the name "D400" which is to be assigned to R1C1:R5C2.
The code I have is:
Range("R" & DRow & "C1:R" & DRow + 4 & "C2") = "D" & Cells(DRow, 2).Value
but is throwing up the error:
"Run-time error '1004':
Method 'Range' of object '_Global' failed
DRow increases dependant on other variable data.
Also, am I right to be using R1C1 instead of A1, or can the same be achieved using A1 references? If so, I think I may not be able to see the wood for the trees.
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
View 8 Replies View RelatedIs it possible to assign values to letters (eg. a=1, b=2, c=3... z=26) and then count the total sum of those letters in a cell
Example
A1= car
car is c=3 + a=1 + r=18 which would make the total to be shown in A2 3+1+18=22
With this code i would like to create a subtotal of all letters per row and a batch total of the column with subtotal.
I have a range of data on one worksheet that is pulled into a pivot table on another worksheet. Looking at the table below I would like to assign the values in the pivot table to a 1 if there is data. For example if any field is >0 I would like to assign it a 1. If not leave it blank. I tried using the formula function in the options tab but didn't have any luck.
Car
Truck
SUV
Color
Black
1
15
Red
7
1
Blue
1
5
I have two tables below. First table contains the list of domain names (given just for example, actual list may contain several more such Domain names) with the group names assigned to them. Second table shows the criteria for assigning them the group names:
Sheet2ABCDEFG1Table 1Table 22Domain NameGroup NameDomain NameGroup NameDomain NameGroup Name3NCG-W3K-SAT14-0DOSSNCG-W3K-EEC-SUN16-3EECEXCEEC4SVL-WIN-SAT08-0LABSNCG-W3K-SUN16-0DOSSDCDC5SVL-WIN-SAT06-0LABSNCG-W3K-TUE01-0DOSSNCGDOSS6NCG-W3K-MON04-0DOSSNCG-WIN-EXC-SUN21-0EECDMZETIS7NCG-W3K-EXC-MON04-3EECNCG-WIN-EXC-SAT16-0EECSVLLABS8NCG-W3K-EXC-MON03-0EECNCG-W3K-X64-DC-SAT14-0DC9NCG-W3K-EXC-TUE01-0EECDMZ-NCG-W3K-X64-SUN12-0ETIS10NCG-W3K-EXC-SUN15-0EECDMZ-NCG-W2K-SUN12-0ETIS11NCG-W3K-X64-EXC-SUN16-0EECNCG-W2K-SUN20-0DOSS12DMZ-NCG-W3K-SUN11-0ETISNCG-W3K-MON03-3DOSS13NCG-W2K-DC-SUN11-3DCSVL-DMZ-WIN-SUN12-3LABS14NCG-W3K-X64-DC-SUN10-3DCSVL-DMZ-WIN-SAT10-0LABS15SVL-DMZ-W2K-SUN12-3LABSDMZ-NCG-W3K-SUN12-0ETIS16NCG-W3K-EXC2-MON03-3EECDMZ-NCG-W3K-SUN10-0ETIS17NCG-W2K-SAT17-0DOSSNCG-W8K-X64-DC-SUN10-4DC18NCG-W3K-X64-SUN10-0DOSSNCG-W3K-TUE01-3DOSS19NCG-W3K-TUE00-0DOSSNCG-W3K-DC-TUE02-0DC20NCG-W3K-X64-SAT15-0DOSSSVL-WIN-SAT11-0LABS21NCG-W3K-WED00-0DOSSSVL-WIN-SAT07-0LABS22NCG-W8K-X64-SUN19-0DOSSNCG-W2K-SUN17-0DOSS23NCG-W3K-EXC-SUN20-0EECNCG-W3K-SUN20-0DOSS24NCG-W3K-ULZ-SUN14-0DOSSNCG-W3K-TUE22-0DOSS25NCG-W3K-TUE03-0DOSSSVL-WIN-SAT07-3LABS26NCG-W3K-ULZ-TUE00-0DOSSNCG-W3K-MON05-3DOSS27NCG-W3K-SUN19-0DOSSNCG-W8K-SUN18-3DOSS28NCG-W3K-EEC-SUN19-0EECNCG-W8K-SUN22-0DOSS29NCG-W3K-EEC-SUN20-3EECNCG-W8K-X64-DC-SUN11-0DC30NCG-W3K-EEC-THU00-0EECNCG-W3K-SUN17-0DOSSExcel 2007
My concern is to automate this process using a VBA code. How can I search for the keyword given in Table 2 in the first column of Table 1 and assign the group names as it is assigned currently, considering the below conditions and criteria.
1) The correct group name should be assigned to the Domain name containing the keyword given as per criteria in Table 2
2) It is possible that the domain name will include more than one keywords given in Table 2. For example Cell number A7 contains both keywords NCG and EXC, in such cases the preferance should be given to EXC and we will assign the group name as EEC. And in Cell number A12 also contains both NCG and DMZ, again the preferance should be given to DMZ and we have to assign group name as ETIS.
That means we will give second preference to keyword NCG. If domain name contains none of the keywords except NCG as per given criteria then only we will assign grup name DOSS.
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?
View 4 Replies View RelatedThe 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
[Code]....
I am trying to randomly select 10 data from a table by using index formula. But there are some blank cell in the table so It kept returning 0 in the cell.
problem.xlsx
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 RelatedI am using a ComboBox on a worksheet where the items in that box are pulled from Sheet1A:A. When the selection of the item in the comboBox is made, I would like several textboxes on that same page to be auto-populated based upon info derived from cells adjoining whatever row in Column A was chosen on Sheet 1.
For simplicity sake, let's say the range on Sheet 1 is A:H, and TextBox1 = "B", TextBox2= "C", etc.
so if,
A B C D
JOHN 15 DOGS BLUE
SUE 18 CATS RED
BOB 20 TURTLES YELLOW
When Combobox selection is JOHN, I want TextBox1= "15" TextBox2= "DOGS" TextBox3= "BLUE"
I would like to know how can I plot my x axis of a chart with the maximum and minimum value that I have in this variable. Note that my variable is no going from the lower value to the highes one, it is random. So what I did is to use the autoscale of the chart, but it is not working as I would like. Any solution?
The code that I use is:
Code:
' TO GET THE INITIAL and FINAL VALUE and the STEP
Worksheets(wsheet(27)).Activate
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory).MinimumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MaximumScaleIsAuto = True
ActiveChart.Axes(xlCategory).MajorUnitIsAuto = True
xScaleMin = ActiveChart.Axes(xlCategory).MinimumScale
xScaleMax = ActiveChart.Axes(xlCategory).MaximumScale
xScaleStep = ActiveChart.Axes(xlCategory).MajorUnit
'After I use them (xScaleMin,xScaleMax,xScaleStep) for several charts, in this way:
ActiveSheet.ChartObjects(chartName(11)).Activate
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text =VarName
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Select
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Font.Size = 11
ActiveChart.Axes(xlCategory).MinimumScale = xScaleMin
ActiveChart.Axes(xlCategory).MaximumScale = xScaleMax
ActiveChart.Axes(xlCategory).MajorUnit =xScaleStep
' but if my data was between 50 and 250 it is plotting between 0 and 300
I have a large excel sheet with the following format:
ColA ColB ColC...
55430 4 45
55431 5
55432 4 7 25 36
...
and i need to convert it as...
ColA ColB
55430 4
55430 45
55431 5
55432 4
55432 7
55432 25
55432 36
Assign values to ratio buttons
I have created a UserForm in Excel, to gather data for a questionnaire.
For each question, the user must select an answer from a radio button (Yes, No and N/A)
When I add the record (answers) to the sheet1, the results are shown as all being false.
I want to show either ‘Yes’ or ‘No’ or ‘N/A’
Private Sub CommandButton2_Click():
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 1).Value = UserForm1.OptionButton1.Value
LastRow.Offset(1, 1).Value = UserForm1.OptionButton2.Value
LastRow.Offset(1, 1).Value = UserForm1.OptionButton3.Value
End Sub
I have an issue with some excel spread sheet I am working on.
I am using Macros, Buttons to assign values to a text box.
When I do not protect the worksheet no problems.
When I include my calculation sub into my Worksheet Change module.
It sort of works but flashes a lot, and takes about 6 or more seconds.
When I dont include it in the Worksheet change module it doesn't flash and seems to work fine.
When I protect the work sheet, in my code I unprotect it and try to run my code, but it wont go through. I get an error.
Run-Time error '1004': Application-defined or object-defined error
Worksheet Module
Option Explicit ' Force the declarations of all variables in this module
Private Sub Worksheet_Change(ByVal Target As Range)
'Unprotect
'Call Calculate_Decision
If Range("MoreBorrowers") = "Yes" Then
Rows("21:27").Hidden = False
Else
Rows("21:27").Hidden = True
End If
If Range("GuaranteeYN") = "Yes" Then
Rows("159:167").Hidden = False
Else
Rows("159:167").Hidden = True
End If
I have tried adding in the Me. before my Range but nothing seems to work.
I want to assign a cell a formula that changes base on variable, example
myVar=3
Cell(1,1).value = "=average(1:{myVar})"
what do i do to make this work instead of {myVar}?
Is it possible to assign values to names in a list, so that when you validate it as a drop-down list, you can select a name from the drop-down and it's corresponding value will be added to separate cell? Basically, I have survey questions which have five possible responses: Excellent - Very Good - Good - Fair - Poor. We want to assign a value to each, 5 for Excellent, 4 for Very Good and so on, so that when a response is selected from a drop-down, it's value appears in a separate cell (so that we can calculate a total and average score from the selections).
View 5 Replies View RelatedI am working up a workbook template that will be used to process survey results. The way I ultimatley want it to work is that the user can paste data from another application into a worksheet in Excel, and then view analysed results on a second sheet (which are processed via formulae on a third, hidden sheet).
The problem I have is this: the data from the program that's capturing them are text values. For example, column B contains answers to a question where participants rate an experience, and the values are either "Excellent", "Good", "Fair", "Poor", "Awful". I want these to be converted to numerical values, on a scale where "Excellent" = 5 and "Awful" = 1, so that I can then average these.
So, the question is: Can I somehow tell Excel that "Excellent" = 5 and "Good" = 4 etc, and then use AVERAGE(B:B) on the text data and get a number back?
I did think of having an intermediate sheet that used VLOOKUP to create a copy of the first sheet (where the user pastes the text data) with the text replaced by numbers. But, because I don't know in advance how many rows will contain data (i.e. how many survey results there will be), I have to assume on the high side and copy down 50,000 rows. But, this takes ages to calculate.
How can I assign values from a listbox to a twodimensional array?
and next
do something like search and remove a row and assign the array back to the listbox
I know I can do it directly, but I need the array for something else too
I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
I have a formula that counts all instances of the letters a, b and c in a range and assigns them a value of 1 unless they are in the M column in which case a, b or c counts 4.
=SUM(COUNTIF(J2:AB2,{"a*","b","c"}),OR(LEFT(M2)={"a","b","c"})*4)
This worked well but now I need to modify the weightings across this range. My new goal is to count all instances of a,b and c in the range J2:AB2 but have M column a,b, c's = 2 and N:R column a,b,c's equalling 4. Letters other than a,b or c count as 0.
Example:
J2...K2...L2...M2...N2...O2...P2...Q2...R2...
A....B.....B.....C.....B.....D....B.....C.....E
the output of the above sequence should be
1+1+1+2+4+4+0+4+4+0 = 21
My company is soon to be rolling out a new payment system, and I'd like to be able to track commission for everything I sell. Briefly, we get paid via a percentage of the company's gross profit, and the way in which the company gets paid is tiered. (the cellphone business)
So, If a customer activates a plan thats below $39.99, we get paid X amount. If it's below $59.99 we get paid Y amount, and so on and so forth, there are multiple different tiers.
What I want to do is set up a list via data validation so that I can pick what plan they have (via the name of the plan) and have it return a numerical value (ie $39.99) and also have the ability to pick text messaging, etc as options so that the workbook will add the monthly rates (39.99 + 14.95) and then have it return value for the right priceplan tier.
If this is confusing, I apologize for not being more clear, but attached is an example. The top one is what I'm actually trying to code, but the bottom is completely filled in, so you can get a more clear sense of what I'm trying to do. Honestly I'm not even sure if it can be done.
What I need to is create a VBA function with several parameters that reads data from an M x N range of rows and columns (matrix). I cannot just pick and choose certain cells within this range as the function goes into a loop through at least 200 cells in the 35 x 200 range. Obviously, I am here posting as I cannot get this function to work. I believe my troubles lie with reading this range into the function...
Application.Goto Reference:="range"
rangearray = Selection.Value
values(I, J) = rangearray(I + 1, J)
Do I need to set a function variable equal to that range, or do I do this another way? Basically, I need to have the ability to pick a given cell(m, n) out of the range and have my way with it in the function.
I've built the code for a Template form for data entry of a survey. Through Control Toolbox I've created the form and used several textboxes, option buttons, checkboxes and COMBO boxes this one in turn being my trouble.
- Sheet one is called "Data" (this is the place where all inserted information is going to be stored
- Sheet two: "Variables" (here is where I keep the required values for the como boxes - inserted manually apriorely...
Question: What is the code to assing to the combo boxes in order to have the values from the sheet "Variables" of the range A1:A4 - use the comboboxq2 for referee, I'll do the others
P.S. see the code up to now:
Private Sub CommandButtonN_Click()
Dim eRow As Long
Dim ws As Worksheet
Dim inf As WorksheetFunction
Set ws = Worksheets("Data")
Set ws = Worksheets("Variables")
' Find first empty row in database survey
eRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row ................
Here's my situation.
I will post a rudimentary example:
Account Name/Column A
Truman school, San Diego
Truman school, San Diego
Truman school, San francisco
Truman school, Atlanta
Truman school, Atlanta
In column B, I would like it to assign a unique numeric id based on the TEXT only.
So Truman school, San Diego gets a string of numbers: 1234
Truman school, san fran: 1235
Truman school, Atlanta: 1236
My spreadsheet is about 6,000 records with a great deal of the above situation going on. So school names are identical with different locations assigned. I am trying to import to a database and don't want a lot of duplicate records. That's why I'm trying to assign a numeric value.
I have a list of grades they are 3a, 3b, 3c, 4a, 4b, 4c, etc up to 7a. An a is one grade than a b, and a b is one grade higher than a c. In one test a student may score a 4b and in another a 4a. I need a way to calculate the difference in "grades" e.g if a student scores a 4a in test 1 and a 5c in test 2 the difference is 1.
Is there a way I can do this using custom formatting or will I have to assign values to the grades and insert extra columns to do the calculations. I need the simpliest way as I have to disseminate to my staff in my department.