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.
How do I assign a particular function to buttons contained in a Msg Box?
I have set up a Msg Box on a sheet so when you go to close the sheet the box opens and asks you if you want to open another sheet contained in the same workbook. The buttons are Yes and No. If the User clicks on the Yes button then Sheet 2 will (If I can figure out how)open from the same workbook. If No is chosen then the workbook will close. I have the Msg Box set up and working but I need to find out how to assign the functions to the Yes & No buttons.
There are 10 rows of data, and would like to assign value at the end of each row as "initiated" "In the process" "Needs to be Reviewed" "Completed", by using Buttons with different macros.
I have several Activex buttons in sheet "Options" and I would like to automatically assign each button to the same macro "Run_Options", in order to execute the macro "Run_Options" when I do click over any button.
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.
I 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:
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?
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).
"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.
I 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.
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.
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.
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.
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.
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...
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 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.
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:
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'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 ................
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.
I'm trying to create a quote... basically I need to find out about calculating ratios.
We have 47 students going on a trip to different venues, we offer 1:10 teachers (i.e. one teacher free, for every ten students going), however some venues offer a different ratio e.g. 1:5 teachers free of charge.
I need to somehow create a column where it will calculate the amount charged for the different ratios the venues offered.
