I have four variables a1, a2, cr and s in the macro below.
Code:
Public Const cr As Long = 4
Sub Mode5()
'4 are the constants you can vary to test different scenarios
Const a1 As Single = 0.1
Const a2 As Single = 0.4
Const s As Single = 3
It is easier if think of these variables in this format (a1, b1, cr, s).
So taking the code above as an example they can be written as (0.1, 0.4, 4, 3).
Ultimately I would like to try different combinations of these values to get a desired outcome. Currently I change the values manually each time.
Variable a can range from 0.1 - 2.5 (25 numbers)
Variable b can range from 0.1 - 2.5 (25 numbers)
Variable s can range from 1 - 10 (10 numbers)
Variable s can range from 1 -10 (10 numbers)
write some code to iterate through different combinations of these variables. I presume it would be some sort of loop which increments each variable a certain amount each time until the max value is reached.
For example the first combination would be (0.1, 0.1, 1, 0.1) and the next would be (0.2, 0.1, 1, 0.1) and the next (0.3, 0.1, 1, 0.1) etc and the last one would be (2.5, 2.5, 10, 10).
Altogether there can be 25 x 25 x 10 x 10 = 62,500 combinations.
I want to check 3 columns of data in one worksheet where all 3 criteria have to be met, and where the 3 criteria are met I want to put those details onto a list in another worksheet. Example:
A B C 1 Red Bag 1 2 Blue Shoe Complete 3 Green Bag Complete
I have used SUMPRODUCT after advice from this site to find the line where all 3 columns meet the criteria if for example I wanted to search which lines have "Red" and "Bag" and "1", 1 being a code for missing, in this case, paperwork. What I want to do is check column C on all lines to find a 1, and any that are found I want to list A and B for that line on a separate worksheet to create a report of current outstanding paperwork
I noticed that sometimes if I stop a macro midway (completely stop, not just pause), and then re-run it from the beginning, my variables will still hold the value they were last set to right before i stopped the macro. But sometimes, this doesn't happen - the variables reset.
I'm wondering what defines how long the variable's value are held for? I'm considering writing a macro that can hold variables so that even after the macro is finished, the user can re-run and the variables will still be available. This is useful mostly for text boxes in userforms that will probably have the same values the next time the user runs, but the user can still change them if he wants.
I have a spreadsheet where in the past we have used a turnover period (always 12 yrs) as our constant to calculate our average factors. For example we use formulas to calculate up to 12 years and the we just restart at year 13- providing that one of the formulas does not return a 0 value. I need to be able to choose our turnover period- say 8 years and have the values start then but in the next prokect I may need a 10 year turnover.
The attached table mimics the massive table that I am working with.
I wanted to populate values of two variables.
For e.g., I wanted to populate the yellow portion of the table with the value "A"; blue portion with the value "B"; Green portion with the value "3/1/1993" and so on (different colors were used to aid my question only, there are no such colors in the actual table).
I have about 700 such portions to be fill in.
My approach: I just started to copy and paste manually which is tedious.
Question: Would there be any automated way to do this?
I have declared a number of string variables named "hlpYear1", "hlpYear2" etc in the 'Worksheet_SelectionChange' function. "hlpYear1" contains the appropriate text to display if the selected language is English, "hlpYear2" is for French and so on. I was hoping that I would be able to use VBA to display the appropriate text by way of a 'For..Next' statement along the following lines:
trying to get it to put the 2 variables into 2 cells in the worksheet for the moment!
The Forms code is:
Private Sub CmdCancel_Click() Me.Hide Unload frmMsgBox Exit Sub End Sub
Private Sub CmdOK_Click() Me.Hide End Sub
It always fills the cells with False and blank, no matter what buttons are pressed (except cancel), so all I can think is that the information isn't feeding back through properly from the form.
I understand the use of Global variables in the sense that they can be used throughout the project. However, I also understand (I think) that unless these variables (like any) are released from memory or the values changed by some other means they will retain their values when accessed inside a another procedure regardless of where the calling procedure is Public or Private. This seems to be a convenient way to access the values of these variables without having to make ByVal or ByRef declarations in the procedure calling process.
Firstly, is my understanding of this correct? (That the variables and associated values) can be accessed from within a calling procedure without formally passing them in?
Second, even if this is true is it considered to be bad practice because the variables will store the last known value which may or may not be correct if you aren't paying attention to how the variable was last set.
A growing dataset representing a group of people being nominated in different weeks for different jobs in different locations. Within a week and over several weeks an individual may be nominated for any number of jobs, in any number of locations.
"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 have 2 worksheets. The first sheet has data of which some needs to be copied to a second worksheet. The trigger is a value found in column E. If a match is found, then a copy statement needs to be built. The values in F, G & H are the values to be used in the copy statement. The content of cell H may be numbers or letters or both.
What is the correct format of the copy statement?
Dimension all variables Dim RowPointer As Long Dim wbContrib As Workbook Dim wbMaster As Workbook Dim SheetName As String Dim target_sheet As String Dim target_cell As String Dim Target_value As String Dim CellAddr ' ' Initialize variables Set RowPointer = 1 Set wbMaster = “Master.xls” ' '******************************
I have a worksheet which I've set to read-only, I am trying to get it to pull in information from external worksheets. I've created a Macro that will automatically bring in the data however it will only go into a pre-defined cell/row. I have another Macro which looks up the next available row (which is where I want the data to go) but can't get it to link into the macro's. In short, Macro1 brings in data, Macro2 finds next available row, Macro3 brings in another worksheet, Macro4 finds next available row etc etc.
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 query about using Array Variables in excel VBA. I have a set of lookup tables and a main data table. The data table will be downloaded everyday. I need to replace the ID's in the main table with actual data from the Lookup tables.
e.g. Main Data Table Color Operator 005--325 005 004--326 004 003--327 001--328 002--322
Lookup Color 001 - Red 002 - Blue 003 - Green 004 - Violet 005 - White
I have a huge amount of data in the main table. So I can't use a lookup formula for automation. Rather I would like to use VBA to create array variables, scan and copy the lookup data into the array and replace the ID's in the main table.
Only that I am unable to achieve this using arrays. I am very basic in executing code related to Arrays.
I've a big problem getting the Constraints in excel solver to use variables. It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....
So the question is. How to use varialbes in the FormulaText argement other than string values as integers?
I have some buttons in different sheets in an excel file, each button has its own code, that is the reason I can not move the code related to each object to another location (sheet or module).
And I have one piece of code in Module1 (Auto_load) in order to execute automatically this routine every time file is opened. Inside "auto_load" routine I initialize some values of some check buttons,options buttons and positions of some objects in diferent sheets, but I can not pass the value of variables between Module and Sheet's code even when I declare as public variables and/or function.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0". At moment I use following
So, my question is, i tried to put the (1/31/2001) and (26/11/2007) which is in the above URL which is separated in variables and the URL remain the same
I am trying to use COUNTIF with two critera. If this isn't possible is there any other way possible of doing this in a range of cells.
What I am trying to do is show the amount of students in a year group who spend x amount of hours on the internet and have a target grade (for example) of Lvl 4
I have been trying use a formula along the lines of =COUNTIF (Q5yr7, "0- 1Hour", Q12yr7, "4")
vlookup with 3 different variables, for example cells k4 k5 and k6 can be changed to give different variables. Is it possible to have a vlookup function in cell k9 which returns the correct % when the 3 variables are chosen. example, blue boat 48 would return %value of 21%
I am trying to count the status and type of some work so:
Column A would contain the status of the work e.g. open, in progress, closed etc. Column B would contain the department: ict, development, operations, etc.
I want to do a summary that shows: How many are in ICT are open, closed etc.
I can do a countif to get the total open, in progress etc or total number of ICT jobs but not ICT In progress.
I am trying to add an additional criteria to the following sumproduct formula. The formula below works fine to add up values that are within a date range. However, I want to add values within a specified date range as well as one additional variable. The additional variable is in column G.
I want to make an excel funktion than can distinguish between 3 variables. The three variables are the outcomes of my first function which are 0, 1 and 2 these IF functions can be seen below: =IF(B11>52;"1";"0") =IF(B12>5;"1";"0") Then when I have these results I have tried the following function: =IF(C11+C12=2;"6-12";"4-6") By using this function I get an output for 2 (true) and 0 (false), but I would also like an output for 1 (which would be 6 in my work) It looks like this: Var 1 58 1 Var 2 4 0 Suggestion 4-6
So in the above case where the sum of var1 and var2 is 1 it is only counted as false for not being 2 and therefore = 4-6 instead of what I would like it to be = 6 (for 1)
I'm trying to count cells in one column that match a variable only if it also matches a variable in another column. For example, I want to count all of the cells in column A that match "Franklin" only if column D shows "True".
