Automation Of Solver: Must Inputs Be Derived From Cells?
Jan 20, 2010I'm trying to automate the solver using a vb program. I have found this example which is just about spot on for my needs:
View 4 Replies
ADVERTISEMENT
Solver Automation VBA Code
Dec 31, 2008 I've done quite a bit of research in the past week or so on the topic of solver automation using loops; while there are a few good posts scattered around, i haven't quite found the solution to my problem.
i'm trying to run the solver on a few like-sized datasets contained in one worksheet. i've set up a loop to properly reference the 4 datasets. the solver constraints are in one column on the same worksheet.
the macro runs with no errors, but it doesn't "solve." the parameter cells retain their original values.
i should mention that my macro ran perfectly before i consolidated the 4 separate solver calls into one loop. I got tired of having to type 4 different constraint changes in the code as i was optimizing the solver.
on a similar topic, does anyone know how to call a variable in the FormulaText declaration? If i wanted to step through a defined array of constraints, say [1,2,3], how do i get FormulaText:= variable to work?
Sub Macrosolver()
Dim iteration As Long
Dim i As Integer
i = 8 'start in row 8 at first dataset
Hiding Formulated Values Derived From 0 Value Cells..
Dec 7, 2009I have a sheet with cells that will vary and change in value. The far right column (in this case G) is calculating a percentage from cells in columns D and F. At the moment only 9 rows are being used but I have made provision for the list to extend down to row 100 for future use. Cells in columns D and F are receiving their values from sumif formulas on another sheet, therefore a lot of cells are showing a 0 value for the moment (which I have formatted to show blank).
My problem is that I have cells in column G (percent formula) which are of course showing #DIV/0! due to the fact that they are calculating cells with 0 value. How can I format the cells in column G to show blank until they receive a real calculation!
Cell Addresses Derived From Concatenated Values In Separate Cells
Apr 2, 2013I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
Solver- When Solver "changes Cells"
Jan 21, 2009I 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?
Under Solver, The Changing Cells Box Only Alllow 200 Adjustable Cells
Apr 26, 2007Under Solver, the changing cells box only alllow 200 adjustable cells. I had problem that my adjustable cells is more than 200. Is there anyway I can increase it ? maybe using VB.
View 2 Replies View RelatedGoal Seek Automation On Range Of Cells
Apr 1, 2013I wanted to set up a macro to automate goal seek on a range of cells. I've attached a short extract below of the spreadsheet I'm working on (the full one has a few thousand lines of data) which I hope illustrates what I'm trying to do.
jan
feb
mar
apr
may
jun
jul
aug
Avg
New Avg
0
0
0
0
0
0
0
0
0
0
34
20
43
43
43
43
43
43
35
39
[Code] ...........
Multiple Solver Constraints In Solver
Sep 10, 2006I 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?
Description Outputs And Inputs Cells In Workbook
Feb 1, 2014I have a small problem here with my worksheet. In my college i need to make a project about sales in a wooden floor shop.
I have done one part of work but actually i'm stuck at the specifications of my sheets.
What I need to do i added the screenshot with design Screenshot 2014-02-02 01.29.03.png
For my project i need to describe everything in my workbook as you can see in screenshot, i have made some notes but i'm note sure if its really right my teacher don't want or can't explain me well this part of work, i don't know why...
Now the question> i would ask some nice people if they can check my sheets and show me where i need to put inputs and outputs notes in the design sheet. Also in the screenshot I surrounded a part with red line there is a very strange thing no one know what is it 3 teacher telling different things.
Automation - Applying Conditional Formatting To Group Of Cells In Loop?
Jul 18, 2014So I would like to automate the following, as doing it by hand takes a lot of time. I have searched the whole internet and finished empty handed.
Start from cell E10
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Move 3 cells down
Select cell
Apply double entry conditional formatting
Move 3 cells down
Repeat above until cell E5000
Reset Dropdown Lists Based On Other Inputs / Cells
Jul 1, 2009Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify...
'Menu 1' has two option: 'A' and 'B'.
If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'.
If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'.
Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'....
'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
Restricting Values Of Changing Cells In Solver?
Sep 25, 2011I have 50 share returns for 2009 (A2:A51). I am solving for optimal portfolio by allocating weights to my 50 shares in 2009. in solver, i am changing cells B2:B51, but the criteria is that if any of the 2009 returns is greater than 75 then the weight on that share should be 0. i tried to write an if function in column C of the changing cells but solver overrides the command when i solve.
View 4 Replies View RelatedChanging Fill Colour Of Cells Based On Other Cell Inputs
Jun 20, 2014I have a calendar set up with dates going along P6:CU6. Column C9:C27 has various status' for the job such as "Complete" or "In Progress" etc. Next to that in rows D and E I have start and end dates for the job. What I am trying to do is change the fill colour of the empty cells that are in line with each job, so that between the correct dates they are the corresponding colour to the status. So a complete job on row 9 that started on 26/05/14 and finished on 28/05/14 would have three green cells along Q9, R9, and S9.
I have tried an IF/AND/OR statement which looks like this:
=IF(AND(Q6>=D9,Q6<=E9),OR(IF(C9="Planned","P",""),IF(C9="In Progress","IP",""),IF(C9="Late","L",""),IF(C9="Complete","C","")),"")
I am getting a #VALUE! data type error, I have tried reformatting the cells to various date formats but no luck so far. This could be because my dates are set up as =P6+1 etc. but I am not sure. The error only occurs when the cell falls within the dates, otherwise it remains blank.
I intend to hide the value in the cell and use conditional formatting to change the cell colour based on the hidden input.
Once this is working I also need to take weekends into account, column G has any 2 value combination of Y and N to say whether the employee is working Saturday, Sunday or both. Row 7 has a "1" value underneath every Saturday and a "2" underneath every Sunday. I can imagine how this would work but combining it with the above formula could be complicated.
Running Calculation X Times With Changing Inputs And Placing Of Output Into Cells
Jan 28, 2014I have a cell in sheet1 (say, B2) in which I can key a date. Once I change that date, cell C10 updates (after I hit F9 to recalculate) with a new number based on the new date.
I have a list of dates on another sheet, from 1/1/2009 to 12/31/2013, all in column B. I want to produce VBA code that will run through each day, 1/1/2009 to 12/31/2009, in sheet1 cell B2 and take the output in C10 and place it next to each day in column B until it reaches the end (12/31/2013). I know this is possible but for some reason am having trouble coming up with the right looping mechanism.
You can imagine if I had to manually key in each day to get the desired output how tedious that would be.
VBA Solver: Find Minimum Value Of Cell By Changing X Cells With Constraints
Mar 18, 2009I'm trying to use solver to find the minimum value of a cell by changing 5 separate cells. These cells are subject to a set of constraints that are formulae. On top of that I have a large number of these solve's to run (The spreadsheet is creating randomly distributed data to form a Monte Carlo simulation). Therefore I am referencing the formulas using the ActiveCell, Offset() function. For some reason when running this code none of the values are changed. Also I changed the UserFinish to false so I could see what solver was doing at each solve, It stops at the correct cell (one after the final cell) but without me having to confirm it each time.
Here is the ....
Solver Solver To Calculate Through VBA
Jan 12, 2007I 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.
Right Axis Derived From Values?
Feb 28, 2014The goal of this chart is to show how sales are adding up relative to bonus levels for the month. The problem for me is in getting the right axis tick marks, labels and chart lines to match the values in Columns C and D. This need may be further complicated because the target amounts increase by a fixed percentage (11% in this sample, but it can vary).
View 4 Replies View RelatedPossible To Have A Sheet Name Derived From A Cell?
Nov 12, 2009Is it possible to have a sheet name derived from a cell?
I am creating a workbook where the users will be able to edit a list of names, and add certain data to each person. There will then be a separate sheet for each person that gives analysis if their individual data. The user will change the names in the list as people come and go, and want the sheet names to match.
Conditional Sum Derived From A Date Interval
Feb 21, 2010I'm trying to sum each months visa purchases. The data is in three columns: date,value and payment type.
I can't get this formula to work:-
{=SUM(IF(AND(Purchases!C6:C999>DATE(2009,12,28),Purchases!C6:C999<DATE(2010,1,29)),IF(Purchases!K6:K 999="Visa",Purchases!H3:H999)))}
It's returning a value of 0 (and if that were true I'd be delighted!)
How To Use VLOOKUP For Cell Value Which Is Derived By Formula
Mar 6, 2014I'm wanting to lookup the content of a cell, which is a string, "Name", resulting from a formula within that cell.
For example the first code is referencing the cell output from the second formula.
Code:
=VLOOKUP(N2,RandomTab!$A$2:$E$60,5,FALSE)
where N2 is
Code:
=UPPER(VLOOKUP(B2,OtherTab!$A$1:$H$68,8,FALSE))
presently it, the first VLOOKUP, returns #N/A
Cell Retains Value Derived From Dynamic Function.
Jan 14, 2009Let's say I have three cells; A1, B1, C1. A1 = a random number (RandBetween 0 and 100) B1 = If(A1>99,1,0). I want to increment C1 everytime I refresh the randbetween via f9 so that the first time A1 returns a value of over 99, C1 picks up a value of 1 from B1 and then the next time I get a value of A1 over 99, C1 adds the value of 1 from B1 to its existing total, giving a total of 2 etc etc etc.
Essentially, what I want is: If A1>99,C1=C1+1. I can't work out how to get C1 to hold its value when I refresh the sheet. I realise it's probably pretty simple, but it would appear that so am I.
Reverse Pivot Table :: Recreate The List It Was Derived From
May 14, 2009I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.
I added a sample to show what I mean.
SaveAs Method With Filename Derived From Cell Reference
Jul 24, 2012I am trying to use a cell reference to have excel automatically Save a worksheet using a cell reference as the workbook name. When I step through the code and use the immediate window I get the correct name for the file (fname in this case) but as soon as Excel tries to save the file, I get an error.
Here is the code I am using:
fname = Sheet3.Range("I1").Value
ChDir "S:Op CostsBudget 2013Data"
MsgBox "The actice file will be saved as " & fname
ActiveWorkbook.SaveAs filename:= _
"S:Op CostsBudget 2013Data" & fname & ".xlsm", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Evaluate Formula Derived Empty String In SUMPRODUCT?
Dec 10, 2013I've been trying to get this SumProduct formula to return anything except an error for a while now.
=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17
IE Automation
Aug 3, 2009I'm trying to do some automation of a website that has one of those annoying javascript popup dialog boxes you have to click OK on before you can progress. How can I kill that so that I don't have to manually click on "Ok" so that my macro can keep running?
View 9 Replies View RelatedVBA Into IE Automation
Feb 20, 2010You take an input, click a button. This will them create a small HTML table with a text input box and another button.
Sub VisitWebsite()
Dim ie As Object
Dim tcode As String
Dim sURL As String
Set ie = CreateObject("INTERNETEXPLORER.APPLICATION")
sURL = http://sample.jsp
ie.navigate sURL
ie.Visible = True
tcode = "apples"
While ie.busy
DoEvents
Wend
Application.Wait Now() + TimeValue("00:00:03")
For Each ieForm In ie.document.forms
ieForm(1).Value = tcode
ieForm.submit
Set ieForm = Nothing
Exit For
Next
The code ieForm(1).Value = tcode works. The code ieForm.submit also hits submit on the button.
The problem is I cannnot figure out how to access the 2nd button which is generated when you click the 1st button. I have tried every variation of ieForm(1).Value. There appears to be only 3 forms (0, 1, 2). When I try 3 or greater, I get an error.
When I use ieForm(2).Value = "something", the Retrieve button text actually changes name to "something".
I've attached a screen shot of the webpage. I can insert text into the top Track Code box, and click Retrieve.
I can't change the text in the Routing Position field, and I cannot click the "Set" button. Any help would be greatly appreciated. I am still a beginner with VBA and it took me a while just to get it to work with the 1st input box.
Adding Values Between Two Dates And Dates Are Also Derived By Formula?
Dec 9, 2013I have attached an excel sheet for your reference. I have particular debit values that are to be added between the dates. And Dates are also derived by formula based of payment term.
The ones I need to modify is Highlighted in Yellow. The values to be added is in "Customer Statement" and in H Column
These dates also have formula by which there are derived
-------------------------Current Ageing-------------------------
Date Range
Bucket
Amount
Percent
Start Date
End Date[code].....
I am USing =SUMIFS('Customer Statement'!$A:$A,'Customer Statement'!$H:$H,"=" & E11) but does not work.
Regression Automation In Vba
Oct 2, 2008The above is a screenshot of the data analysis (regression) I want to automate with vba code. Like all macros, I tried to record first and only got the following
View 3 Replies View RelatedEmail Automation
Feb 11, 2010I am trying to create a document that automatically sends an email whenever a field reaches a certain value. I am pulling the data from different worksheets onto a summary worksheet and using the data on the summary sheet as the point at which my code checks to see whether the value is below the minimum required. The summary sheet updates automatically whenever I change the data in one of the other sheets but the email will not send automatically when the summary sheet is updated. When I run the macro individually the email sends, however I need some way of triggering it so that it sends automatically upon the data in the field changing. Can anyone help me with this?
Here is an example of some of the code I am using:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("D3"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value < 1 Then
ANDES1
End If
End If
If Not Application.Intersect(Range("E3"), Target) Is Nothing Then
If IsNumeric(Target.Value) And Target.Value < 1 Then
ANDES2
End If
End If
End Sub
Automation Error After Some Iterations?
Jun 11, 2014Creating a spreadsheet to inventory cups each day. When a macro was invoked do the following:
-unprotect the worksheet
-cut/paste the completed sheet below the current
-bring in the values form the previous day's inventory
-set protection on new spreadsheet to lock the cells
-blank out old values
-reprotect the worksheet
Pretty straightforward and seems to work fine for a few iterations, then crashes with "Run-time error '-2147417848 (80010108)': Automation error. The object invoked has disconnected from its clients.".
As FYI, this code was mostly generated using a record macro session, so no variables involved.
Here is a screenshot of the spreadsheet as it sits now:
spreadsheet.png
Here is the code:
Sub NewDay()
'
' NewDay Macro
' Create new day and copy cup count from previous day[code]....