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?
Dim iteration As Long
Dim i As Integer
i = 8 'start in row 8 at first dataset
I'm trying to automate advanced filter but can't get past having just 1 critreria. I have attached an example spreadsheet where the download dump is on "Data" sheet and the result of the advanced filter is on the "Result" page. I would like, if possible, for the result to come up every time the criteria is put in (they are all data validation lists).
so for example, if i select account code, all those codes come up then select period and the list dwindles down and again for cost centre. Is it possible to do this without using macros. I have found the following code butnot sure what to do with it (sorry).
So I haven't seen this error before. It says "Automation error: the object invoked has disconnected from its clients." I think I know what's causing it but am not sure how to go about solving the problem. Barring any inefficiences that the code below contains, can someone please tell me what code I should used to fix this error?
my excel sheet runs through different cases (i=1 till i=20). For each case I need to use the solver. Since the whole programm works with references like this cells(10 + i, columnindex).value I tried this with the solver, too. e.g.: SolverOk SetCell:=Cells(20 + i, 46), ValueOf:="0.000001", ByChange:=Cells(20 + i, 47)
But it gives me not a right result as it would give me with a reference like SolverOk SetCell:="$AT$21", ValueOf:="0.000001", ByChange:="$AU$21" I tried difference referencing things with different types of brackets, wih range, etc. but I could not figure it out hwo it might works.
Suppose that you have some data points and you want to fit a straight line to them. You write the line's equation in the cells ajacent to your data points, make the square of each difference, sum up all the squares and you have your objective function. Of course your line equation depends on two parameters, that you enter in two cells. Then you run the Excel Solver, enter your objective function as target cell to minimize, enter the range containing your parameters, set your constraints and off you go. So far so good.
And now suppose that in order to generate your model data it is not enough to write a straight line equation in some cells, but rather to run a complete reactor model (contained in a macro). How do I couple that model-generating macro with the Excel Solver? Now, I actually found a solution. I wrote a Worksheet_change macro in the sheet containing the model parameters, which calls the model-generating code each time the parameters are chenged by the Excel Solver. This has worked fine for a simple example as the straight line one.
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?
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.
You 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.
I 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
Creating 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:
Here is the code: Sub NewDay() ' ' NewDay Macro
' Create new day and copy cup count from previous day[code]....
We are trying to automate the creation of PDF files for each sheet in an Excel file,a nd keep getting error messages. Can someone help? It produces the post script file, but it has no data nd then doesn't create a PDf file at all. We have Excel 2003, and have the below code setup.
I have a file that I update daily. It involves opening the file, going to a drop down menu and selecting 1 of the listed items and pressing a button to run the macro. Once the macro is complete, I then go back to the dropdown menu and select the next line item and press the same button to run the macro on this. I repeat this task 7times.
All the macro does is load a txt file to a third party system.
I am looking to see if it is possible to reduce the time it takes to do this task.
I am trying to automate a website which I use for daily office related work. I am navigating the website, selecting some options and clicking on download button using vba code. When I click on download it opens a new internet page. Now I have to click one more hyperlink on that newly opened internet page. How to do that?
I created a UserForm (userform1) and added a label to it which essentially just says: "Pick a Colour"...
The other UF controls are added on the fly using the below code...however when I try to add the event to each button I get the Automation Error but I don't really understand how / why... the events are actually added to the VB project ok...
Private Sub UserForm_Initialize() Dim NewButton As MSForms.CommandButton Dim ws_i, line As Integer Dim clr_str As String For Each ws In ActiveWorkbook.Worksheets
I added a dummy routine for test purposes which is to be invoked on click of UF button control (I have tried placing this code in a standard module, within UF module etc to no avail):
Sub HIDE_WS(clr_id As Long) MsgBox clr_id End Sub
So to reiterate -- when I invoke the initialisation of the UF I get the following error:
Automation Error The object invoked has disonnected from its clients
All events are added for each control.
If I remove the adding of the events I know the UF populates with the correct controls etc...
pivot chart automation. I have a tab that contains thousands of rows and it grows larger on a weekly basis. On this tab is 169 peices of hardware I manage. Each week 1 row of data is added to all 169 peices of hardware. The data from the main tab contains metrics I need to chart. However, I am in need of a way of selecting a specific peice of hardware and having the pivot charts update automatically. There will be 26 Charts and going through each chart and selecting the proper hardware is going to take far too long.
Is there any way i can select just 1 peices of hardware from the data tab and have it populate all of these charts?
how to automate AddIns sequence when opening Excel? I have 5 different AddIn (xla) and I have to launch them in a specific order (after the menu Essbase is installed - with xll) cause I'm adding rows in that menu. Excel is launching my xla's in alphabetical order but before Essbase is installed.
I receive sales data from my wholesalers every month and I continually have to format them to fit the structure of our in-house database. I wanted to design a macro that would automate this process. However, in some months, the files are recieved in a format that is a bit different from the wholesaler's usual format.
Is there such thing as an initial "litmus" test where I could try running the macro and if it doesn't fit the usual structure, there's an error code and I could do it by hand?
I am new to excel macros and writing automation code....I work for a company that sells special permits and so far i have written a macro to bring up a pop up box when a permit has expired but i also want to be alerted by email, how can I do this, also is there anyway to stop the macro from running once it finds all of the expired permits? I usually press ctrl + break....
I have attached the file to this post : Book1.xlsm
I am trying to analyze a web page and would like to search through the contents of a web table. I need to extract text error messages which can be located at various unpredictable rows / columns in the web table.
My situation so far i have a rather large program in vba that accesses a site (confidential info im afriad will post snippets though) logs in navigates through a rabbit warren of pages using different types div's form's a href's etc and finally get to one page before where i need to extract information from!!
This webpage has one large table then nested tables within for each entry.
I need to find a way to search the data cells within the nested tables find a specific displayed value then if required tick the checkbox (which has dynamically generated id's names etc) in that row then move on to the next table and do the same.
All I need to do is establish that one row has the data value i need and tick the check box that correlates (which is in the same row).
I have an Excel report that I'm wanting to know if it can be automated. Basically it starts off in Access, I have a few queries that run via an Access Macro, once the queries are done there is one query via the macro that exports a file of data to excel. I then have a 2nd excel file that is a pivot table that I refresh from the exported Excel file of data from Access. Once refershed, save it, upload it to a Sharepoint Site and send out an email via Outlook. Basically I'm wanting to know if this can be automated to were it runs at like 6am or something before I even get on my computer. I'm not a vba expert, I can do some basic stuff but what takes most people an hour or so to do would take me a day or so. Also would this be easier to just have the pivot update directly to the Access DB?