optimisation

I have a spreadsheet that is used to manage the annual budget along with monthly forecast, monthly resource costs, vacations etc. in separate sheets in the same workbook. So needless to say there are lots of formulas in these sheets and references across sheets. I am also using named ranges and...

Hi, So I have this macro which normally runs in about 10 seconds but with my self-made add-in installed it takes about 4 times longer even though the two have nothing to do with eachother. Also I turned off events, screenupdating etc. Is this a known thing and what can I do to solve this...

Hello, I run a lot of macros that have one big table as an input data, that is later tranformed into separate tables by making pivot tables (using different column/row sets and usually different filter). Since my upgrade from Office 2016 to 2019, all of them started to work very slowly. I...

I am working on a project to automate importing multiple .dbf files into a single excel sheet. The idea being I can import a group of .dbf files representing one dataset and the trend/graphs I need are automatically generated. The .dbf files always have the same data columns/header (38 columns)...

Hi guys! first time posting here. I'm french canadian so sorry about my poor english. So basically i wanna make like a little database on Excel with notes/jurisprudence/doctrine/laws and for every entry that i add on this sheet, i'm giving the notes/jurisprudence/doctrine/laws a tag on the...

Hello! I want to create this model in Excel to find the optimal solution with 2 constraints (Labor Hours and Material Available pr. week), below you will see the description and the solution in AMPL. For my own learning in other courses, I wish to make this in Excel using Solver. "Packing can...

Hi experts, I am trying to identify the optimal solution on the below problem. Have tried using solver but without luck given the amount of variables. Thus, I hope someone can guide me on how to solve this with VBA or another solution. Situation: I have - 200 balls in 6 different colours -...

Hello, I have two sheets: one with raw data, and one where I'd like to do some analysis. In Sheets("raw_data").Range("W:W") I have measured values. I would like to copy only the non 0 values to Sheets("analysis").Range("A:A") . The way I'm doing it now is: Sub copy_data() Dim lastrow As Long...

Hello, I have a spreadsheet with error codes (column A) and timestamps (column B) . Every time there is an error, it is recorded and time stamped. I would like to write a macro that writes in another column (column D) the frequency of each error. I have filled column C with the distinct error...

Hello, I have a spreadsheet with a column containing timestamps (format dd/mm/yyyy hh:mm:ss). I am trying to select certain dates: within a certain month, within a certain year etc. The month I want to select within is selected in a combobox (format yyyy/mm). The way I'm doing it is comparing...

Hi All, I am new to excel VBA - but I do understand after I read the code. I have the attached spreadsheet where I have sheet "Combinations" with all possible combinations for P1-P5 items. While the best combination is ranked based on Value, I want to create an output for 52 weeks in a year...

Hi, I am struggling with quite a complex problem in which I think I may be able to solve or at least partially solve using the Excel solver tool. I am trying to set up a design of experiments analysis in which I have a number of parameters at two levels. I have one specific objective which I...

Does anybody know the reason for the lag before calculation in a large workbook? And potentially how to resolve... Specifically, when I change a particular input it takes a minute to complete the calculation - during that minute I can look at the Processes tab of Task Manager in Windows and...

Hi MrExcel members, I've been lurking the forums for a while for tips for writing my first macro, but I finally decided to make an account to ask for help optimizing my code. I feel that my code is pretty inefficient and, while the end goals are accomplished, I have to go through somewhat...

Hi MrExcel members, I've been lurking the forums for a while for tips for writing my first macro, but I finally decided to make an account to ask for help optimizing my code. I feel that my code is pretty inefficient and, while the end goals are accomplished, I have to go through somewhat...

I have a large set of data, about 50,000 rows by 25 columns. I need to filter down the data into 10 groups based on different criteria. I have tried 2 methods, both are taking the better part of 2 hours to complete. I am hoping someone can think of a faster way to do this. For the first...

I first want to apologize for dumping a lot of code into here, but I have been stuck on this issue for days. I was assigned to build a planning screen wherein I could pull corresponding information regarding Sales, Production, and Inventory. As a preface to all of this, this code works for me...

Lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row For i = 3 To Lastrow Sheets("sample").Range("AM1000000").End(xlUp).Offset(1, 0).Select Selection.FormulaArray = _ "=IF(ISNUMBER(MATCH(1," & Chr(10) & " (order!R2C15:R1000000C15=RC[-24])*" & Chr(10) & "...

Hello I need help with an optimization routine<o:p></o:p> I have a 60 X 20 matrix of values which when combined with 20 factors generates a weighted average value for each row of 60 entries.<o:p></o:p> I have a target range (upper lower bound) for each row and would like to quickly develop an...

I have got a very large dataset on which I need to perform some data analysis. There are roughly 100 variables but there are multiple iterations so in the end it is closer to 15,000 variables. I need a way to select just the variables with the same header in a new worksheet as that will make it...