optimisation

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...

Hello everyone. The Problem ========== I am working on a speed date project. The "who meets whom" part is already solved, and the output of that is a full list with all the pairs that will be meeting that day. The actual speed dating day will consist of **N rounds**, and will have **P...