Iterating Through Different Values Of Variables
Oct 28, 2011
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.
View 5 Replies
ADVERTISEMENT
Jul 11, 2006
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
View 6 Replies
View Related
Jan 27, 2009
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.
View 3 Replies
View Related
Sep 28, 2006
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.
View 3 Replies
View Related
Jun 5, 2013
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?
View 4 Replies
View Related
Jan 3, 2010
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:
View 4 Replies
View Related
Jan 15, 2009
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}?
View 9 Replies
View Related
Nov 30, 2006
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.
View 4 Replies
View Related
Mar 24, 2014
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.
View 2 Replies
View Related
May 13, 2008
My data: four variables A,B,C and D.
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.
A=week number (52 possibilities), B=job code (20 possibilities), C=location (2 possibilities), D=name (500+ possibilities) .....
View 9 Replies
View Related
May 23, 2009
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.
View 9 Replies
View Related
Aug 30, 2006
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”
'
'******************************
View 9 Replies
View Related
Oct 18, 2006
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.
Sub Macro3()
With ActiveSheet.QueryTables.Add(Connection:= Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsjspencerDesktopExcelAMMH.xl" _
, _
"s;Mode=Share Deny Write;Extended Properties=""HDR=NO;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database" _
, _
" Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking................
View 2 Replies
View Related
Jun 6, 2007
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)))))))))
View 3 Replies
View Related
Nov 20, 2012
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
Lookup Operator Table
323 - Operator 1
324 - Operator 2
325 - Operator 3
326 - Operator 4
327 - Operator 5
328 - Operator 6
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.
View 2 Replies
View Related
Feb 9, 2009
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?
View 14 Replies
View Related
Feb 5, 2009
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
View 2 Replies
View Related
May 13, 2009
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.
I have the following structure: ...
View 11 Replies
View Related
Jul 27, 2006
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.
View 2 Replies
View Related
Apr 27, 2006
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 ..................
View 3 Replies
View Related
Jan 16, 2007
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
vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0
vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0
until...............................
View 3 Replies
View Related
Apr 9, 2014
I'm having a hard time understanding how to accomplish what seems to be a simple result.
I need to display one of two words, based on whether or not a pair of values are above or below the criteria.
FIRST:
IF H6 is greater than 5000
AND
IF AB6 is greater than 25000
Display: Double
SECOND:
IF H6 is less than 5000
AND
IF AB6 is greater than 25000
Display: Single
There is no 3rd scenario, even though logically there should be.
View 13 Replies
View Related
Apr 1, 2008
I am trying to put variables in this URL which is related to yahoo finance :
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
I defined at the beginning
Dim start_date As Date
Dim end_date As Date
Dim datestring As Variant
start_date = #1/31/2001#
end_date = #11/26/2006#
and put them in datestring
I passed the datestring to a new sub which has the URL:
.Name="hp?s=NVDA&a=00&b=31&c=2001&d=11&e=29&f=2006&g=m&y=0"
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
View 11 Replies
View Related
Jul 6, 2012
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")
View 9 Replies
View Related
Jun 26, 2014
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%
View 2 Replies
View Related
Aug 1, 2014
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.
View 1 Replies
View Related
Feb 19, 2008
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.
SUMPRODUCT(--($A$3:$A$1015>=$A1026),--($A$3:$A$1015<=$B1026),D$3:D$1015)
View 10 Replies
View Related
Nov 30, 2008
Trying to work out the formulas for placing plus minus variables above and below a cell as per worksheet attached. right hand side of the page
View 2 Replies
View Related
Jan 20, 2009
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)
View 13 Replies
View Related
Apr 14, 2009
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".
View 5 Replies
View Related