Solver & Integer Calculation Slow
Jan 4, 2007
I have a large Solver scenario (100 vars, 150 constraints), which is working really well, but slowly. It's pretty straightforward, using only integers for calculations, but takes a LONG time to find an answer.
What I was really wondering is why Solver doesn't just use strictly integers in variable cells that are constrained as integers? I'm sure it's inherent to the way Solver runs, but I know it would make integer calculation WAY easier and faster.
View 3 Replies
ADVERTISEMENT
Mar 26, 2012
I want to set constraints as an integer in solver. But I don't know how to do it.
View 5 Replies
View Related
Dec 5, 2009
I got a work sheet with 672 columns of information that im trying to cross compare against. I wanna compare each column against every other column in that row. I have 200 rows of data. That means each i need excel to do 226,128 comparion calculations each row. So that means in the entire work sheet its gotta do 91 million comparisons. Im on a dual core 1.8ghz core 2 duo cpu and 2 gigs of ram on xp pro with excel 2007. I even bumped up my virtual memory by 3 times the size it was yet still its taking forever.
Its taking over 3 hours to do this whole page of calculations. So i opened up visual c++ and quickly programmed in the same code with some generic values and within 3 seconds it computed it all. My guess is that the bottle neck is when excel has retrieve data from the cells because other than that i cant figure out why its so slow. Heres a section of my
View 14 Replies
View Related
Oct 1, 2006
I am making a small push button calculator to enter data into a textbox on a userform in an add-in file and this works fairly slowly. I am trying to concatenate a list of numbers in a textbox that simulates a calculator screen when entering numbers.
Private Sub CommandButton6_Click()
Dim Val
Val = "6"
Dim valand As Range
Set valand = ThisWorkbook.Worksheets("Stageing").Range("K65536").End(xlUp).Offset(1, 0)
valand = Val
Dim A As Range, B, C, d, E, F, G, H, I, J
Set A = ThisWorkbook.Worksheets("Stageing").Range("K2")
Set B = A.Offset(1, 0)
Set C = B.Offset(1, 0)
Set d = C.Offset(1, 0)
Set E = d.Offset(1, 0)
Set F = E.Offset(1, 0)
Set G = F.Offset(1, 0)
Set H = G.Offset(1, 0)
Set I = H.Offset(1, 0)
Set J = I.Offset(1, 0)
TextBox1.Value = A & B & C & d & E & F & G & H & I
End Sub
View 4 Replies
View Related
Sep 10, 2006
I have a cell, D5, which is the sum of three other cells, A5 B5 and C5. (all currently empty). Cells A1 through C4 are filled with various numbers.
What I've been trying to do is use solver to say: Make D5 equal 200, do it by manipulating only A5 B5 and C5, and make it subject to the constraint that A5 must equal a value selected from A1:A4, and B5 must equal a value from B1:B4, and C5 ...etc. I have deliberately set it up so that there is only one solution.
I was doing fine until trying to create the constraints. How can I make a constraint that says "this cell" must equal "one of the following cells"? And if I can't do that, is there an alternate method of achieving the same result?
View 9 Replies
View Related
Jan 12, 2007
I have to use use the solver to calculate something (a mean-variance framework).
I am using the solver to minimize a cartain cell (variance) by making two cells equal through (expected return) by varying 10 cells( weights of assets), but I have to repeat this for 500+ times (for different expected returns).
Someone told me that I could best use some sort of loop through VBA. But I don't have a clue how that works.
View 13 Replies
View Related
Jan 21, 2009
I want to use solver program. But when solver "changes cells" i want it to trigger my pivot tables in the workbook. So i added the code to my worksheet:
Private Sub Worksheet_Change(ByVal Target As Range)
ThisWorkbook.RefreshAll
End Sub
So when a change occurs, all my pivot tables will get refreshed and my data will change. Is solver able to trigger this event while solving an optimization problem?
View 9 Replies
View Related
Apr 16, 2007
I received a reply somewhere
Dim i As Integer
is better used as
Dim i As Long.
Integers get cast to long in the OS, and then back to Integers when passed nback, so it is a unnecessary overhead.
but didn't want to hi-jack that thread
QUESTION:
why would we still use "integer" then?
I tested with
Sub testInteger()
Dim starttime As Double
starttime = Timer
Dim i As Integer
For i = 1 To 32766
Dim j As Integer
For j = 1 To 32766
Next j
Next i
View 9 Replies
View Related
Sep 12, 2009
I have been working on a basic input sheet for progress reporting at work to standardize the information and acheive a half decent automated report.
I am have trouble validating the textbox to only allow an integer to be entered in the box. On result of text being entered on add, a msgbox should prompt the user to enter only text.
How might I do this?
Private Sub CommandButton6_Click()
Dim iRow As Long
Dim ws As Worksheet
View 9 Replies
View Related
Dec 8, 2009
how can i sum integer like
1548=1+5+4+8=1+7=8
View 9 Replies
View Related
Oct 6, 2008
I have a problem with vlookup. Here is an example:
A1= 1.3
Then my data array would be something like this
C D
1 a
2 b
3 c
4 d
My problem is that the reference for the look up is a non-integer, but the data in the lookup is. How can I make this work?
View 2 Replies
View Related
Oct 17, 2009
Below is a snapshot of data in a column. I need a way to extract the number, may be a whole, may be decimal, and leave behind the text description. Result to be put into a new column:
Hernia Umbilical 553.1
Perirectal Abscess 566
Diverticulitis 577.0
553.21 Hernia Vental/Incisional
Abdominal Pain RUQ789.01
Abdominal Pain RLQ789.03
217: Benign Breast mass
541-appendicitis
Chronic 575.11 Cholecystitis
SBO560.81
786.05SOB
226/ Thyroid neoplasm
The idea here is that the integer could be integrated in anyway imaginable with the associated text. I don't need the text. The end result must display this in a new column:
553.1
566
577.0
553.21
789.01
789.03
217
541
575.11
560.81
786.05
226
View 10 Replies
View Related
Mar 30, 2009
i need is a script that will add two boxes together to get a sum in a third box, but where it is tricky for me is that i need the third box to retain the summed value even when either of the first two box values are deleted.
For example, say i have box A1=10, B1=40, and C1= SUM of A1 & B1 (so C1=50). Easy enough, but where im lost is that i want to be able to delete the value of either A1 or B1 and then have C1 still = 50, so then i can input another value into either A1 or B1 to continue to add to C1.
View 6 Replies
View Related
Aug 27, 2009
I'm creating a user form where the user can enter latitude and longitude in either Deg/Min/Sec format or Decimal Degrees. To go from decimal degrees into Deg/Min/Sec I need to do the following
A=Decimal Degrees (Single)
X=Degrees (Integer)
Y=Minutes (Integer)
Z=Seconds (Single)
X = Integer(A) <-- I need to cast A as an integer or truncate it. Not sure how to do this in VBA. Y = Integer[( A - X ) * 60] <-- Again, needs to be cast as an integer Z = {[( A - X ) * 60] - Y} * 60. I didn't see a native truncate function in Excel and in the past I've always used Left() or Right()
View 3 Replies
View Related
Apr 9, 2004
I am using the IsDate() and IsNumeric functions in my VBA code. Is there an equivalent function for testing if a value is an integer?
View 5 Replies
View Related
Jan 22, 2009
I'm trying to format a number inside of a chain of a string. So,
Worksheets("TASK").Cells(task_sht_current_row, task_sht_act_id_column) = Cells(current_act_row, work_package_column) & "-" & Cells(current_act_row, cti_wp_column) & "-" & current_act_step_num
So I'm trying to format the last variable so that it is always four digits, ie, 0001, 0034, 0204, 1234, etc...
Any clue how to do this, I've searched around and can't seem to find the code for it. I could do it with a long set of conditional statements, but I'm pretty sure there is a format function or something.
View 9 Replies
View Related
Jun 15, 2009
While reading the book "VBA and Macros for Excel", I can not understand the following
Dim concat As Variant
concat = 0#
What does the # indicates?
View 13 Replies
View Related
Feb 14, 2008
I am trying to drive Solver in Excel 2000 with a Visual Basic (version given as 9969) macro. SolverReset is observed, and SolverAdd puts the constraints in. But the SolverOK statement does nothing, so the SetCell and the ByChange windows stay empty.
SolverSolve is trying to do something, because the spreadsheet cell that should receive the return code from Solver gets an error value #VALUE! (The macro then stops, unable to process this entry.)
Solver is checked off ok in the References list in V.Basic.
The same macro, same spreadsheet, in the same Excel file, works fine on the other computer in my office with what I am told is a more recent version of Excel; i.e. this doesn't look like a coding error.
View 4 Replies
View Related
Feb 21, 2013
In the following code snippet I have worked out a way to verify that the user inputs an Integer and then prompts downstream if an improper value was entered. The user is prompted for a corrected reponse and loops until they get it right. The initial Cancel at the first prompt was causing issues until i saw poster: titarelli use StrPtr(). Except for subsequent cancels, the code works but it is clunky at best. How to tighten this up?
VB:
Dim Quantity As Variant
Dim CorrectedQuantity As Variant
Dim j As Integer
j = 194
Quantity = 0
CorrectedQuantity = 0
[Code] ......
View 2 Replies
View Related
Feb 23, 2009
Take a look at the sample sheet. I get the information as an import that looks like the info in column A.
I want it to look like the data in Column B. If I try to format as a number it always turns the last number to a 0. I also dont want the 0's on front of the digits either.
I dont really care how its done. But I would like a routine to turn column A into and like the example in Column B. No zeros on the front and maintaining all numbers.
View 9 Replies
View Related
Oct 12, 2009
How to make A1= A2xA3 and round the result to the higher integer that can be divided by 6 ????
View 3 Replies
View Related
Feb 9, 2010
I'm trying to write an if statement that checks if a value is a whole integer value or not. I was initially using the Mod operator but realized that the Mod operator rounds values to whole numbers before determining the outcome. Initially I tried:
View 3 Replies
View Related
Nov 4, 2011
I need to count, not list, all of the factors of an integer. For instance the number 12 has the following, 1 2 3 4 6 12, so the solution to the NoF(12)=6.
View 9 Replies
View Related
Feb 5, 2012
I would like to round down to interger or half. I would give you some examples:
3.76 to become 3.5
2.48 to become 2
-1.12 to become -1.5
0.05 to become 0
I tried to use round down fucntion but I am not able to have the desired outcome. I thnink the issue is with the second argument of the aforementioned function. How many digits should be indicated? I put 1 but it does not work.
View 9 Replies
View Related
Dec 17, 2012
I set a column width to some integer like 10, and the next time I open the file it has changed to something like 9.89. Is there some configuration that prevents this from happening and leaves my setting alone?
View 4 Replies
View Related
Dec 30, 2013
My macro generates a number in the range 1 to 90 and places it in cell A1, I would then like to hear the number in speech.
View 1 Replies
View Related
Sep 19, 2007
I have defined array say like this:
Dim myarray (1 to 5, 1 to 2)
Is possible to define something like this?
myarray (x, 1) as string
myarray (x, 2) as integer
x is anything between 1 to 5 .
View 9 Replies
View Related
Oct 22, 2008
I want excel to generate a random integer between 132 and 321 and place it into cell B10. What formula would I need to enter into cel B10 to do this
View 9 Replies
View Related
Dec 1, 2008
A1 : Style 998 45702B WRAP DRESS 1 0 0 0 1
Try to accomplish:
I would like to remove the "1" in between "Dress" and "0"
in other word remove integer from string but starting from right only.
B1 = should contain 1
View 9 Replies
View Related
Sep 19, 2009
I am trying to concatenate some string and integer. It is always for previous month end date, i.e, 7/31/2009 or 8/31/2009.
Here is I have the data now
Profit and Loss for
After running the macro I would like to be
Profit and Loss for 8/31/2009
Here is what I wrote so far which does not work. I am sure there is simpler way to do it. I put the month end date 5 columns to the right for the time being and in the intention after deleting it after concatenation automatically.
For Each cell In Selection
ActiveCell.Offset(0, 5).Value = "=EOMONTH(TODAY(),-1)"
ActiveCell.Offset(0, 5).Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yyyy"
View 9 Replies
View Related
