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 tracked down an overflow Error 6 to the following line:
dMax = Int(s * DPoints / SPoints)
Where all variables are defined as Integers. The error goes away when I define s as Long, but I don't understand why this should be required since none of the individual variables ever exceed the scope of an Integer (-32768 to 32767).
Strangely, I get the same overflow problem when I type either of the following calculations in the immediate window:
? Int(328 * 100 / 1000)
? 328 * 100 / 1000
? 328 * 100
Note that no variables are defined, so why the overflow problem? Surely Excel/VBA should be able to work with this very basic calculation without any problem, by assigning the appropriate dimension, no?
Is this a known problem with Excel/VBA?
I am using Windows 7, with Excel 2010 (updated from 2009).
is it possilble to extract integers from a text string within a cell?
Eg.
Cell A1 - "ABC 123"
I want to extract the "123" from cell A1.
I have many text strings from which I need to extract the integers from.
The trouble is that each text string does not follow a consistent order, so I
cannot use text functions such as LEFT, MID or RIGHT to extract what i need.
I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:
Direct Credit 158824 BRANDON DONNA C/ Lak30
the second example is:
Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade
but that still would not get a match that I can find code for.
i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily
I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.
I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?
Sheets("main").Select
ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text
....
email.Body = messagebody
On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.
Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:
I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:
Private Sub Weeklytestt_Click()
Dim varCurDate As String
If DateYesterday.Value = True Then Answer = "1"
If DateToday.Value = True Then Answer = "2"
If DateTyped.Value = True Then Answer = "3"
Select Case Answer
Case Is = "1"
varCurDate = Format(Date, "yyyymmdd")
varCurDate = varCurDate - 1
Case Is = "2"
varCurDate = Format(Date, "yyyymmdd")..........
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).
I am trying to automate a report writing spreadsheet in which the text never changes in the cell (up to and over 50 words long).
Is there any way in which the figures within that text could be linked to another cell and change accordingly.
For example: a particular cell might contain the following which never changes apart from the figures:
"the performance of the fund was (B5) followed by a change in the index of (B6)...........the performance of the irish stocks was (A19) etc etc."
I know something can be done in formatting but for very long text would this be impracticle?
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 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 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 RelatedThe 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:
View 4 Replies View RelatedI 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}?
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 have a variable called "MinVal" which is an integer. Does anyone know the correct code for rounding this integer to the nearest multiple of 5 (or 10)? I need to do this in VBA, not on the Excel worksheet.
View 4 Replies View RelatedI 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.
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) .....
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.
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.
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................
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 am trying to add some numbers only if they are integers/numbers. My condition goes like this:
IF((rangeA)=Numbers,SUM(rangeA),"One or more values not available")
I came up with this formula, but it gives me an error.
=IF(ISNUMBER(P5;Z5;BL5;BN5;BP5;AS5;AV5;EU5;FI5;GH5;GK5;GN5;GX5;HE5;HR5);SUM(P5;Z5;BL5;BN5;BP5;AS5;AV5;EU5;FI5;GH5;GK5;GN5;GX5;HE5;HR5);"One ore more values not available")
It says I have given many arguments for ISNUMBER function.
The only way I can find under "Conditional Formatting" to highlight round numbers is to set a formula.
View 1 Replies View RelatedAccording to Microsoft in Visual Basic an Integer can contain -2,147,483,648 to 2,147,483,647.
Yet when I store 5,000,000 to an integer I get an overflow error.
A user would input 4 integers.
How can I generate a list of all possible combinations of the integers?
I am using to rotate through different values of 2 variables. The macro was created by a friend of mine 2 years ago so I need to modify it. The macro is quite usable so I hope someone will benefit from it. The problem with it is doesn’t rotate through a range of integers but only from zero to some number. In other words it allows to specify only the right hand of the range through which it will rotate (for example, from 0 to 100 or from 0 to 20 for A). The second issue with it is that it adds a difference number (“dif”, e.g. 3) to the start of the second variable’s range and starts only from there (from 3 to 100 for B). The third issue is that it doesn’t allow to rotate through a third variable (“C”) as you will see in my example. I am working on a task which requires precise definition of the ranges through which the macro will rotate and also the ability to rotate through a third variable. Need to modify it so that all the three imperfections are removed from it? Or maybe there are ready made macros freely available which allow to do just that?
View 34 Replies View RelatedI 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.
I have a set on 300 values and I want to sum the 290 smallest numbers. Is the a function or combination of functions that can give me that result?
View 3 Replies View Related