efficiency

  1. P

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

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

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

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

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

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

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

    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!
  9. S

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

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

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

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

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

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

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

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

    Efficiency question: SumIfs vs Array

    I have a report I make for every month end. We dump tons of raw data out of SQL and then I slice and dice it into a table that the customer wants. We're talking 200k rows of info across 25 columns. I have to conditionally sum certain columns based on many logical checks on other columns. For...
  18. B

    Code efficiency.. input needed

    I more or less need some input on if this code could be more efficient. If it can be faster please let me know how. All input is much appreciated, even if it is as simply as it's good as is. Dim MyTest As Range, AshF As Range, AshT As Range Dim AshH As Range, AshL As Range, myRng As Range...
  19. D

    An Alternative Approach

    Looking to see if there is a more efficient formula for the following: =IF(F22="pint",CONVERT(E22,"pt","gal"),IF(F22="quart",CONVERT(E22,"qt","gal"),IF(F22="liter",CONVERT(E22,"l","gal"),IF(F22="fluid",CONVERT(E22,"oz","gal"),IF(F22="cc",CONVERT(E22,"ml","gal"),CONVERT(E22,F22,"gal"))))))...
  20. A

    Help making code more efficient

    Good day, I'm looking to make the following code more efficient. Any suggestions would be greatly appreciated! Thank you in advance.

Some videos you may like

This Week's Hot Topics

Top