Can Use Variable Names To Identify / Assign To Other Variables Or Arrays?
Apr 10, 2014
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions
DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
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 am developing a macro to select cells within a worksheet based on certain criteria and to make a list of their range names. My approach is to copy the range names to a column of cells using something like the following text:
but I get an error message saying "Object doesn't support this property or method". Why can't I copy a range name, and is there a way to work around this?
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).
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.
I do have a data set from A1:C8 as follows:- Brand Name(A1)Sales Value(B1)Margin(C1)Levi's100002000Arrow150002500Allen Solly120001300Hanes80001000John Miller250004500Disney280003200Ray Ban180002100
The above brands are categorized as follows:- Brand NameProposed CategoryLevi'sConsignmentArrowBrandAllen SollyBrandHanesBrandJohn MillerPrivate LabelDisneyBrandRay BanConcessionaire
I would like to put formula from D1:D8 in such a way so that the Proposed categories are extracted automatically through formulae. This will help in getting the contribution calculated from different categories. Brand Name(A1)Sales Value(B1)Margin(C1)Category(Desired Result)(D1)Levi's100002000ConsignmentArrow150002500BrandAllen Solly120001300BrandHanes80001000BrandJohn Miller250004500Private LabelDisney280003200BrandRay Ban180002100Concessionaire
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.
Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE Welcome to Ozgrid.
Assume the following: Cell A1: is the input cell Cell B1: contains a number Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting. If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:
Sub openfile(FilePath) Dim myWBk As New Workbook 'Now open Form myWbk=Workbooks.Open(FilePath) End Sub
This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column 2) enters the letter of the column (up to "z", not case sensitive) 3) enters the number of the column
Sub Button5_Click() Dim ColumnUsed As Variant ' find number of columns selected For MyColNum = 1 To Selection.Columns.Count Cols = Cols + 1 Next MyColNum 'find number of rows selected For MyRowNum = 1 To Selection.Rows.Count Rows = Rows + 1 Next MyRowNum..............................
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
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.
How to assign a "WS(i)" worksheet name and have the assigned variable (i) in the name equal the consecutive steps in a total page count.
Here's the code that does not work:
Code: Dim WB as Workbook Dim WS as Worksheet
Set WB = ThisWorkbook For i = 1 To WB.Sheets.Count Set WS(i) = WB.Sheets(i) Next
I can see why it does not work. I can't figure out what to declare in my Dim or how to word the statement using the (i) variable in order to capture the value to attach to "WS."
Details: The issue is that the number of pages created will vary day to day depending on staff needs. There's an array of staff names to be assigned to page tabs and there will be skips and deletions along the way depending on daily attendance so "WS(i)" worksheet names can't be assigned at creation. What happens if they are is that the succession of worksheet names winds up being (e.g.) "WS1, WS2, WS5, WS12," etc. instead of consecutive.
There's a very complex data-sort-and-assign system that depends upon how many staff-assigned sheets there are, and as such each sheet has to have a "WS" worksheet name for the process to stay smooth. If the names are not consecutive, though, the sort-and-assign process becomes confused. This step takes place at the end of the initial workbook set up process, immediately before the data are addressed, so there really isn't an earlier opportunity. It can't be later since from this point on the system depends upon the "WS(i)" names being there.
It started fine when there were 5 people. Now there are 37.
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536 Dim strDataRng As String strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _ strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).