# efficiency

1. ### Delivery efficiency rate and analysis for a logistics company

Hi everyone, I have been trying to create an algorithm to calculate the delivery efficiency rate (DER) of a logistics delivery company. The variables I have currently are late delivery risk( 1 or 0), distance between departure (customer city) and destination city(order city) , time taken for...
2. ### Excel Calculation Performance: IFS vs OR

Hello, I read that an OR function will evaluate ALL logical expressions before returning a result, which seems wasteful because you'd think that as soon as it evaluated a single TRUE expression, it should no longer need to evaluate the remaining expressions. Does the IFS function also evaluate...
3. ### Using a Variable array in place of a Vlookup to return values to VBA code

I have a set of sheets which contain data which I pull into my VBA code with vlookups. The input data is collected into a table of data 10 columns wide, the key field is always an integer between 1 and 3000 but depending on the data entered by the users I might have 5 entries or 3000. The...
4. ### Efficient multiplication in VBA with macros

Hello friends, I am trying to create a code that is as efficient as possible. What I need to do is Multiply a range of cells by a fixed value and then replace the value that each cell had with that new value. For example: In the "D5: 25" range I have different numerical values in each of the...
5. ### Changing the Value of a Cell When a User Clicks a Specific Cell - Need Help With Efficiency

Hi there! I have a program that I'm trying to write that displays a date in a cell based on whether a user clicks on a specific cell. Here are a few notes: The date is formatted as YYYMMDD A4 - Contains the first half of the date (ex YYYYMM) M4 - Displays the final date in YYMMMDD format F/G5...
6. ### Make VBA loop more efficient - needs to handle a lot of data

Hi Guys, So I have a VBA macro which I can rearrange data with but need some help to make it more efficient. So i have a lot of source data (~900,000 rows), populating A2:AD900,000. My code works down column AM which is populated with names. So the code takes the name from AM2 and loops down...
7. ### Slow and Hungry Code

Okay so I've been learning as I go, the below code all works fine... as long as you have a powerful machine to run it and plently of time. Does anyone have any suggestions as to how I can make it more efficent and faster? Sub Printdata() Dim Passport As Worksheet Dim Tally As Worksheet Dim...
8. ### For dynamic sorting / ranking, is there a speed difference b/t the RANK and COUNTIFS methods? (And are there other faster methods?)

I have a large sheet that relies on a lot of live ranking. Is there a speed / efficiency difference between the two formulas below? (Obviously trivial for the tiny data set I used to create the image, but my sheet pulls in live stock data and is live-sorting 1,000+ cells that update nearly every...
9. ### Inefficient coding

Hi all, I built a set of macros that is supposed to consolidate deposit information from four workbooks into one worksheet, and then build an index from four additional workbooks to pair customer names with deposit data. Unfortunately, this macro takes over an hour to complete, and I am unsure...
10. ### Maximizing VBA Processing Speed

Hi all, I'm trying to maximize the speed of my macro, I've included all the tricks I know but it still takes 5 min to process and I'd appreciate any advice on how to optimize the processing of my code. I've narrowed it down to two parts that lag and have included all relevant code below. The...
11. ### I need a faster way to verify if a date is between two dates

Hello, I have a table and need to check whether each cell in a row satisfies a certain condition. One of the rows has a date. The date might need to satisfy an upper bound or lower bound. Moreover, the comparison might need to be strict(< or >) or not (<= or >=). I have the ability to encode...
12. ### help with if then statement

https://docs.google.com/spreadsheets/d/1REi1NFguL-hD4O6_ZDJuel20Z2aybUJPUWA_ZLdJ0Rc/edit?usp=sharing i am really sorry to bother you. i dont understand why this if statement is not working looking to reward efficiency goals with scores of 1, 2, 3 etc etc. please help!
13. ### Conditonal format pivot table based on comparing two values

hello, working on a pivot table conditional formatting and need help. I want to compare each month AVG CPH to previous month and if greater format green and less format red. for example in February NTX cph was higher than January so that would be green and WTX in February is lower than January...
14. ### Inefficient Code

Hey all, Thought I'd give you folks a good laugh, perhaps even a hard cry by taking a look at the "Submit" button i have in my ever growing user form for an Order Follow up system. This is a multitude of other's work stitched and sewn together over the years. Everything is working, and it's not...
15. ### General Efficient Read In of Static Data

Hi All, I have a large amount of static data i.e. it will never need to be changed. It's 146,000 rows by 20 columns. What I typically do is use a macro to read data in from a sheet cell by cell and then store it into an array. Saving the data this way seems to be pretty time consuming...
16. ### How to repeat VBA to the next 4th column on right if there is values on that column and how to highlight text and negative numbers

Hey everyone, This is a basic math inventory and order tracker. Each week 1 inventory is to be imputed, I want the code to repeat as necessary until the next inventory column is empty Also to highlight Negative numbers in RED and Text in ORANGE. anywhere in the big selection within L2 to...
17. ### Process improvement - Is there a more efficient way? 3 excel sheets

Hi may be confusing:confused:, but I'll try my best. The forum and members have helped me out twice before. I am trying to see if the process I'm following is the most efficient. I am working with 3 different excel workbooks which can be combined into one. 1. workbook 1 - the template and...
18. ### Need macro for mass control find replace

Hello, I have a workbook I update every month, and currently I have just been using control find replace to update. However, this takes me 10+ minutes for each of 5 tabs, as there are thousands of cells it needs to replace that is linking to a previous month's file. Is there a way to quickly...
19. ### Retrieving Data from old files to paste in new files via Macro/VBA

Any help is greatly appreciated. To simplify: -I have multiple files for 2016 say files A, B, C, D, E, F (each file is labeled with the year at the end ex. A 2016.xls) -I need to copy certain data (all on one worksheet) for the same files from 2015 (A 2015.xls, B 2015.xls, etc.) into the 2016...
20. ### quick way to control find replace?

Hello, I have a file that I change some linking for at the beginning of every month, but it has THOUSANDS of rows it changes. What I've been doing so far is a control find replace (for example, replace "S:/Jan example" to "S:/Feb example"... etc. but it takes me a very long time. I tried...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back