efficiency

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

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

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

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

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

I have a workbook where there are various groups of cells where users enter values. I have code which translates that worksheet into a nice clean data table. Essentially the code loops through the defined ranges and checks every cell to see if there is a value > 0. If there is a positive...

I have a bunch of expense categories and am using the macro to check if the categories are in a certain range. If they are not in the range, I need to insert a row copying the formulas from the above row and add the category. The code I am using is: RepeatCheck =...

Good morning, We have a number of large spreadsheets which are full of vlookup formulas between multiple workbooks. I have started replacing them with Index Match and this has worked really well. We have a scenario where Index match doesn't seem to work, and i would like some advise on a more...

Hello, I have a worksheet with data similar to the table below: <tbody> (Hidden) Sort Name Cost (Empty for Spacing) 1 1 AAA $20 2 2 BBB $18 1 3 CCC $25 2 4 DDD $15 </tbody> This group of 5 columns repeats up to 300 times across the worksheet. I currently have a macro that will...

This is a bit of an open-ended question. I am using ListObjects quite a bit to render table-like data onto Excel using VBA. I basically pass a two dimensional array of type 'System.Object' from managed code (C#) and through automation, accept it as Variant on the VBA side through a macro. Even...

Hi All, I am relatively new at creating programs for excel in VBA. I created the attached spreadsheet program for my company and wanted some input on how (if possible) this program could be written more efficiently to decrease file size and increase quickness. Thank you so much for your...

Hi all, I'm relatively new to the VBA and the Excel Object Model and a long time lurker in need of some advice on the most efficient way to attack this problem. Any input would be greatly appreciated. Following is a simplified scenario. I've got two workbooks: The first contains 25 product...

I am working with a multi tab worksheet. The first tab is where all the data is input and the rest are a lot of data analysis. When the calculations are running data entry is extremely delayed. When I set Calculation Options to Manual it seems that it does not remain manual if I reopen it. Is...

Hello, Situation: Accounting database that needs to be matched based on the last two columns. Any unmatched rows are manually fixed. <tbody> A B C D E F G H I J K L Type Nbr Post <tbody> Auto Rev </tbody> <tbody> Nbr </tbody> <tbody> Nbr </tbody> <tbody> Date </tbody>...

Hi People, I'm looking to Improve the efficiency of my code. I have two ActiveX Command Buttons, that, when clicked set 50-ish ActiveX Checkboxes to be TRUE or FALSE depending which button is clicked. At the moment the code ticks each checkbox singularly and moves to the next, which is a bit...

I have a large table of data that spans 24 columns and 18K of rows. I want to have an output that scans the mass data and returns 24 columns of only 365 rows (1 year or maybe 2). That will further be processed into a few rows calculating Std Dev, Median, Mean, Percentile, etc. In terms of...

Hi All, I'm still quite new to programming and so don't know all the tips and tricks. Here is some code I have written to summaries customer purchase information. If you can see anything I can change to speed it up it would be much appreciated... Its pretty long and taken me ages to do, but...

I have a large data set of about 375,000 rows by about 6 columns and I have a macro that does a few calculations on the dataset and appends two additional columns for each row. The calculations are pretty mild (basic division, multiplication and a sqrt function). Ultimately, the macro works as...

This is a technical question about IFERROR. I have a few formulas that depend on particular cells to contain data to function correctly. A lack of any data in those cells will result in an error value. Previously, I trapped these errors using the following method: =IF(ISBLANK(D3),"",formula)...

As of yet I am not good at writing efficient codes, but hopefully I'll be some day. The code below exports a lot of data from excel to a formatted word report. How can this code be faster? It takes 90 seconds on my computer! (some of the 'comments are in Norwegian..) Sub wordgenerator()...