Macro For Running Solver ...
Jan 11, 2009I 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.
ADVERTISEMENT
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?
Macro To Run Solver ..
Dec 21, 2006can I program a macro to run solver? ...
View 9 Replies View RelatedMacro To Run Solver
Nov 2, 2006I'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 RelatedSolver Macro Crashes
Jan 25, 2010When 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?
Macro To Run Multiple Solver Solutions
Sep 28, 2008A 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
Macro To Run SOLVER Across Several Sheets On Linear Program
Nov 18, 2012Writing 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
Saving Solver Objective Function Value With A Macro
Mar 23, 2013I 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] .........
VBA Macro To Use Solver On User Selected Column Of Data?
Jul 24, 2014I 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 RelatedPrevent Sheet Change Macro When Solver Runs
May 26, 2009Is 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.
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.
Macro Stops Running The Macro Completely And Hangs In The Middle
Jul 3, 2007Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
Running A Macro When A Value Is Reached In A Specific Cell Used To Call Up Macro
Jan 7, 2009I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
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?
Running A Macro
Dec 19, 2006Can i run a macro by just the pointer going over it.
View 9 Replies View RelatedRunning A Macro On Save?
Apr 10, 2014I'm trying to get a macro to run when the user presses the save button. I have the following code:
[Code] .......
This code is supposed to apply the TRIM function to every worksheet in the workbook when the user presses save, and the saves the results. I understand the Private Sub bit is the code to have the macro run during the save, but for some reason the macro itself isn't actually running. I'm not getting any error message, its just that the cells which should have the TRIM function applied to them aren't being corrected.
Progress Bar While Macro Is Running?
Mar 10, 2014I have a macro that takes about a minute to run. I would like to add a progress bar to appear once the macro starts running. There are no loops in the macro. What are my options?
View 3 Replies View RelatedRunning Private Sub From A Macro
Mar 17, 2014I have 3 different sheets with a private sub on each all labelled
Private Sub CommandButton1_Click()
The macro's runs fine on each page.
I want to put a macro on a separate sheet that i can run each of those macro's
I did initially copy the original private macro and change the
Private Sub CommandButton1_Click()
to
sub report()
But I couldn't do all 3 in the same manner. i did change each name to something different... the other 2 subs did run, but they didn't do anything except put the massage box on the end saying that "the macro has finished".
The private macros are on sheets 26, 28 and 12.
Running Macro In Different Workbooks
Mar 25, 2014I have 7 different workbooks as part of my daily routine.
At present the first workbook is opened via the task scheduler, and the macro is run using the following code
[Code] .....
This then opens the next workbook and closes the current one. The next workbook then runs its macro again from a timed call
[Code] ...........
This repeats for 7 workbooks. The problem is, if a macro overruns - the whole schedule is out of sync when macro's are called depending on the time. What I would like to do, is link each macro by calling for the next one, and closing the current workbook. However, when i have tried to do this, the macro has always stopped when the workbook has been closed - the next macro doesn't run.
Slow Running Macro
Oct 3, 2007Below is the entire code that I am using. It is a simple routine which checks whether a part has started its release process or not, based upon dates. The code works and does what I want.
The problem that I have is that it is very slow, for example it takes 35 seconds to go through 530 lines items. In my (limeted) experiance, based on other VBA doodlings this slow.
Running A Macro From A If Statement
Jul 28, 2008Is it possible to run a macro from an IF command?
View 9 Replies View RelatedRunning Macro Every 20mins.
Oct 8, 2008I already have a macro made named (PowerPoint) that is saved to the wookbook. I am looking for a way to have this run every 20min. I have tried several differnt things and it did not work. Here is one I found online. I only need it to run when it is open and every 20mins. as long as I have it open.
View 3 Replies View RelatedRunning A Macro From A Different Workbook
Dec 18, 2008I have 2 workbooks (Workbook1 and Workbook2).
Workbook 1 contains a button, that calls a macro. This macro calls a macro from Workbook 2.
For this, I am using the Application.Run function.
The Macro in Workbook2 is a simple one line macro that updates cell A2 to a value of 6.
Range("A2").Value = 6
This works fine!
However, I want the the cell A2 in WORKBOOK1 to change to 6, not the cell in Workbook 2.
To add an element of difficulty, Workbook 1 could have any name (I won't know what it is, as the user could change it).
How can I get the Macro in Workbook 2 (Called from Workbook1), to update the cells in Workbook 1.
Confirmation Box Before Running A Macro
Apr 8, 2009How do I ask for a confirmation box before running a macro, but only one time. What I mean is some of the macros I use loop and it wouldn't be practical to click on "ok" for each time the macro ran.
So again I just need some code to tack on to the beginning of a couple of my macros that will ask me before they run but only once.
Macro Running Very Slow
Jun 11, 2009I have the following macro in a worksheet...and it is running very slow. There are other macros in the worksheet and they all run very well. Any ideas by looking at this code why it would be so slow in running?
View 7 Replies View RelatedMacro To Stop Running At Last Row
Aug 10, 2009I have a spreadsheet containing a macro to automatically sum values from week to week and display the max/min and average. This is almost fully working. The problem I have is the macro copies the sum formula down the page and doesn't stop at the last row. It always adds one or two extra rows. The formula should stop at row 77 however this does not happen and therefore the sum keeps going. Which in turn, distorts the max/min figures.
View 2 Replies View RelatedRunning Macro In Another Workbook
Sep 17, 2009I have 2 files in a folder, "Main.xls" and "My Database.xls". "My Database.xls" contains the following macro in 'Module1':
View 2 Replies View RelatedRunning Macro From A Cell
Jul 23, 2014I am trying to utilise a vba script for DNS resolution which i got from Followup: DNS Lookup and Ping in Excel - CodeProject
I have a list of IP addresses that I want to use this on, however if I test this by using the test instructions, excel does nothing, its just text in a cell, so I would like to know how would this procedure get called from the spreadsheet?
Code:
GetHostname("4.2.2.1") in any Excel cell.
or
Use: GetIpAddress("www.google.com") in any Excel cell.
RUNNING AUTOMATIC MACRO
Jan 12, 2007I am trying to achieve an automated macro.
I would like my spreasheet to look at cell B2, compare this to a list of information in Sheet2 and if it matches, run Macro1
Running Macro On Close
Jun 19, 2008I'm trying to run what is a pretty basic macro when a file is closed
Sub Auto_Close()
FileCopy "C:Documents and SettingsdaveDesktopWorking olderPlanner.xls", "S:LeavePlanner.xls"
End Sub
I've checked that the Auto_Close feature is what I need, but it just doesn't seem to be running
I've tried it from clicking the x, and also File>Close, but with the same lack of success
