Writing a macro to run SOLVER on a linear program. I have twenty tabs, each with a linear program and a solver already set up (constraints, cells to change, and cell to maximize). I would like to run a sub that will solve each tab's LP by running the tab's SOLVER...
If it makes it easier, my cell to maximize is I17, by changing B16:H16, subject to I20:I40 SolverAdd -> SolverOk -> SolverSolve
I have to convert the first to the second program, but i don't know how to do this:
MAD.png
ABStolinear.xlsx: the first sheet is the first program which is correct (i think), the second sheet is a failed attempt to write the second program
finding the proper code (which will exist in a larger macros) to check to see if specific worksheets exist. Have looked at functions, Cases, On Error checks and nothing seems to do the job I need it too. Here's what I'm loooking at doing...
Background: Main job of macros is to open a source file and systematically copy and paste data from several specific sheets (8 out of 15) into 'like' sheets in a destination file. Destination file is where the macros is run from.
Before starting any copying or pasting I want to make sure the source file has all the correct worksheets. I've run into cases where users have either deleted or renamed worksheets and as a result my consolidation macros falls down. So the order of operations at the beginning of my macros would be...
- Open source file
- Check to make sure worksheets with proper names exist
- If they do exist, continue with macros
- If even one worksheet can't be found (either deleted, or renamed), then prompt user to check the source file and then stop the program.
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?
can I program a macro to run solver? ...
View 9 Replies View RelatedI'm trying to run a macro which will allow me to run solver and keep the solution.. I have managed to get the macro to work. However, does anyone knows how I can ensure that solver's solution can be automatically saved without prompting the user whether to save the solution?
View 2 Replies View RelatedI have a problem with a macro in Excel 2007. It invokes
solver and creates an answer report. Please find the VB
code for macro, below.
When I run a group of macros for some reason after the marcro runs and I hit F2 excel crashes.
I have figured out that it is one of the marcros that makes the excel crash. It is only when I run the solver application. The code is below. Any thoughts why hitting F2 after running this marcro would crash the machine?
I would like to know if it is possible to run a macro from excel for another program and then have results imported into excel.
Currently I have a program called Reflection WRQ that I run a macro in and the results are captured in a text file. Then I run a macro in Excel that imports that text file. If possible I would like to have my macro in Excel run the command in Reflection and then import the results in Excel.
A single macro that I can assign to a button and, when clicked, will compute 3 solver solutions instantly without the solver boxes popping up.
My problems
I am having trouble with the "ValueOf:=" part. In my code below, I am trying to tell it to go to either cell b14, b15, or b16, select that value which will be a number, and use that number as the "ValueOf". The ValueOf is the number I am ultimately trying to achieve using Solver. Range("B##").select doesn't work but I need to somehow select that number in that cell and tell solver to use that number when computing.
If I manually type a number for the "ValueOf:="0.952975095" it will compute it correctly but manually typing this number in each time defeats the purpose of creating this macro. The whole purpose of this macro is so I don't have to manually type in numbers.
Also, recording a macro with multiple solver steps hasn't worked. When the macro is ran with multiple Solver solutions, it only computes one solution... the last one. I think the last data is stored in the Solver application but I'm not sure how to erase it, and start a new solver setup. Anyways, you all would know better than I would.
What's important?
It needs to compute in a certain order. In this order: Cells D14, then D15, Finally D16. That's probably not a big deal though.
My code
Sub Macro1()
'
' Macro1 Macro
'
SolverOk SetCell:="$C$14", MaxMinVal:=3, ValueOf:=Range("B14").Select, ByChange:= _
"$D$14"
SolverSolve UserFinish:=True
SOLVERreset
I have written this macro below to run solver multiple times. It saves the solver results on a line and then inserts a new line for the next solver run.
I would like to save the final Objective Function Value from solver in each line as well as its result. Is there any way to do this?
Sub run()
'
' run Macro
' run solver
'
' Keyboard Shortcut: Ctrl+n
'
Application.ScreenUpdating = False
Range("B4:T13").Select
[Code] .........
I'm creating a report at work that I want to be able to print out a PDF copy of a worksheet (QPRForm_V6). I have Excel 2007, and have using the code below that works for me, but when people using Excel 2000 try to run this macro, it gives them a 400 error. Everyone has this program called PDFCreator (that is set up in the printer folder).
View 2 Replies View RelatedI've only used VBA to expedite Excel processes, so this is my first exploit into using it for something else. So there's a radio program I enjoy listening to. It's not local, so I have to stream it online. I got a job recently, and I can no longer listen to the program during the day (my job doesn't allow streaming audio). So basically to hear the program, I have an internet explorer add-on called freecorder that records streaming audio. Basically what I've done till now is start recording the streaming radio before work and then stop recording when I get home. That creates quite a large file, so I'd like to set up a macro that does the following:
1) Open internet explorer at noon
2) Go the website from which the radio station streams
3) Click the button that starts freecorder recording
4) At 3 o'clock click the button that stops recording
I need to write one Macro program for below logic.
A
B
C
123
123
‘TRUE’
123
234
‘TRUE’
[code]......
I am trying to write this macro so that I can optimize 181 outputs based on 2 inputs (columns A and B are inputs). Each row has a single output at the end of the row that I am trying to minimize. I want to be able to select as many of the rows that I want and have the code optimize each individual row. So far I can't get it to let me use variable cell references for the "ByChange:="$A$3,$B$3"." This is the hang up that is not letting me increment the macro to the next row. The macro works currently for the single selected cell, but until I can vary the ByChange portion, I can't automate it.
View 1 Replies View RelatedIs there a way to prevent the Workbook_SheetChange event macro from triggering when I run Excel's Solver?
The solver goes through 100 or so iterations changing multiple cells each of which seemed to trigger the Workbook_SheetChange event. If there was a Solver Run event, I could turn off application events, but as far as I know such an event does not exist.
Is It Possible To Auto Save To A File Via A Macro?
What I Want To Do Is To Auto Save Directly To A Folder On Our System Via A Macro.
The Problem Is That Firstly It Will Need To Look Up A Cell ( D8 ) To Find Out The Job Number
Then It Will Have To Find The Folder On Our System ( Its On Desktop Under Job File Folder ) Then Comes The Hard Bit It Will Need To Locate That Job Number ( Cell D8 ) To Look Up The Relevant Folder To Put It In. All The Job Files Are Labelled Up Eg.( J2663 - Parry ) Then In That Folder Is A Sub Folder Called Docs Which It Needs To Be Saved In
I was wondering if there is a way to open up a given program (notepad in my example) ..paste data from cells copied in excel and save the notepad file to a specific location and close it out??
The file path will be given from a data cell in excel as well as the file name.
From searching the forums i've come up with the way to open notepad
using the lines
Dim RetVal As Variant
RetVal = Shell("C:WINDOWSSYSTEM32
otepad.exe")
I open a program using code
Code:
Call Shell("C:Program Files...
After this is open i have to type my password, how can i create a macro to open the program as above then type my password then hit enter.
to sum it up
i can open the program
i need code to type in the open program and hit enter.
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.
I was handed the attached file. understand everything except how the values in row 6 were derived No formula present when I received the file, just the numbers. Row 7 is hard entered scores the units achieved
View 8 Replies View Relatedneed an excel simple formula or function that returns values of y for given x in a two-dimensional array and works in the following way.actually a combination of linear interpolation and lookup/linear search. as in following example
x y
1 10
2 15
3 20
if x=1.5 then y=12.5 (answer available thru FORECAST but incomplete)
if x=2 then y=15 (answer available thru LOOKUP but incomplete)
so for whatever the x i need/get a valid y thru a single formula/method !
sorry m not good in math,
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?
I would like to run a multiple linear regression in vba. I have one dependent and three explanatory variables. I will have to use a macro of some kind, since I need to run too many regressions to do it manually. To simplify things a little bit:
- There will always be exactly three independent variables
- There are no missing values
- The data is allways numerical
I've already got four ranges defined: Yrange, X1range, X2range, X3range. I would like to take these ranges as input parameters for the regression model. The only two parameters I need are Sum Square for Regression (SSR) and the degrees of freedom. I understand that you can use excel's matrix formulas to calculate some of the input parameters, but one doesn't really get around vba. Any (simple) source code allowing me to conduct a regression with three input parameters?
I have 3 equations and have created the relevant chart from them. What I would like to do is put in a value of x & y and get result z. Having done a search through previous threads I found some BiLinear Interpolation code that works using the table in a workbook, but what I'd really like is the Interpolation of the formulae rather than the resulting table and for it to be embedded in VBA.
View 6 Replies View RelatedI am using the Forecast formula to give me a value from a Linear trend. However I need to limit the resulting value that is displayed within a min and max value. Background: This is to allow me to calculate the amount of bonus someone will earn depending on the Percentages of their target they achieve. The min bonus is 25% when 80% of target is achieved and max bonus is 100% when 125% or greater of target is achieved.
So the Forecast formular works great using the existing values, but i need help limiting the results to between 25% and 100%. I was trying to use a sumif formula to copy the resulting cell but only if the value fell between the 25 and 100%. I've attached the spreadsheet as I think it will highlight what i'm trying to do better than I can explain it.
In the attached sheet, I have measured data from a device. It shows Input, Output, gain and delta. You can see as input increases, delta comes down. I want to find the output corresponding to delta od -1. In the table, I have enteries for delta of -0.982 then -1.382, not for -1. How to find output corresponding to delta of -1?
View 6 Replies View RelatedI need a formula to calculate a linear sliding scale from 10%-30% (no higher, no lower). Based on the data in the below chart, if the GROR (let's say this is known and the result resides in cell A1) is less than or equal to 0.0% the payout % is 10.0% and gradually increases as the GROR increases.
The payout % is capped at 30% which is if GROR is 18% or greater. Below are just some examples of how certain thresholds should calculate.
GROR 0.0%5.0% 8.0% 11.0% 14.0% 18.0%+
payout %10.0%10.0%14.6%19.2%23.8%30.0%
If I have a set of numbers 1,2,3,4,5,6,7,8,9 in cells, how can I get a line of best fit, linear regression, in the cells next to them?
View 1 Replies View RelatedI have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.
The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.
