I have discovered a strange problem with my VBA code. After testing it on several machines, I've found no problems with my workbook until now. After being run on a rather slow Windows Vista laptop, I've found that VBA appears to be running faster than Excel, and consequently it is creating errors as VBA asks Excel to do things before it has finished a previous action. This is causing macros to fail several times in each run, though can be solved simply by pressing 'debug' and then F5 to continue the code. Is there a way to get VBA to run slower to solve this? As I can't think of any other way of doing it? I've certainly not seen it happen on any XP or Win 7 computer!
If I want to improve my excel performance like ability to open multiple excel and sheets to perform calculation, should I change to a SSD or a faster intel chip? My current PC sometimes give me a black screen for my excel sheet or is unable to save certain sheets when I open too many sheets.
I'm using the following code to populate the columns next to the dynamic named range dataforgraph (varies in length). It works, but the problem is that it takes ages. Is there a more efficient way of doing it. It is important that it only populates the cells next to the varying dataforgraph. The avg1 and stadev1 are the same for each entry (thus constants)
For Each c In Sheet3.Range("dataforgraph") c.Offset(0, 1) = Sheet3.Range("avg1") c.Offset(0, 2) = Sheet3.Range("avg1") + Sheet3.Range("stadev1") c.Offset(0, 3) = Sheet3.Range("avg1") - Sheet3.Range("stadev1") c.Offset(0, 4) = Sheet3.Range("avg1") + 2 * Sheet3.Range("stadev1") c.Offset(0, 5) = Sheet3.Range("avg1") - 2 * Sheet3.Range("stadev1") Next c
I have an entire row of if statements that basically check other sheets if a number occurs. If it does then it will display "Pending Approval" ,"Open", or "Closed" depending on which sheet the # appears on .. Just wandering if their was a better formula for this..
I have an Excel document with about 40+ tabs that represent different categories of data that I input on different days. I was just wondering if it was possible to have another tab (in the same document) that could accumulate all the data from the other tabs into one sheet so I can see all the data for each day regardless of which sheet it is on?
For example, what I input into Sheet 1 also gets written in the "accumulated" tab and the same for other sheets? And if I delete something from one of my sheets, it also gets deleted on the "accumulated" tab.
I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)
Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.
My spreadsheet is a contract file that includes a list of part numbers being sold. Each part number refers to a lookup table of ALL about 20,000 valid part numbers and prices. Once the contract is finalized, I want to make the contract sheet smaller by deleting all rows in the lookup table that are not required for this contract.
The following code works, but it takes more than 10 minutes to work through the complete list. I read down the lookup table. For each record in the lookup table, I call a routine that reads through an array of the part numbers that are included in this contract. If the lookup table part number IS included in the contract, I skip it. If it is NOT required, I delete it. I then return to the main lookup table and read in the next lookup table record.
This is the main routine where I progress down the big lookup table.
'Work down the Price File range from top to bottom Set RefTableRange = DSWPrices.Range("DSWPriceRange") RefTableIndex = 1 Application.Calculation = xlCalculationManual While RefTableIndex < RefTableRange.Rows.Count RefTableIndex = RefTableIndex + 1 'check if this part number is included in the contract Call CheckRefTableRow(RefTableRange, RefTableIndex) Wend.....................................
I currently use an Excel program (with macros) that run weather forecast data. It is compatible with versions XP, 2003, 2007 and 2010. However, I am trying to figure out if I can run it on a Mac. My plan is to run windows on my Mac through Parellels or VMWare Fusion.
Here is a link to the Excel Program: TX Tornado Forecast Worksheet
I have the following section of code and i am trying to make it faster. Currently when this procedure runs the screen "changes" durings its execution. Now i know i can "turn off screen updating" and this will increase speed but are there commands in the code which will prevent the screen from updating. I know activating or selecting things causes the screen to change. Also, any inputs to make this faster/better would be greatly appreciated.
I'm trying to improve a code that Indexes a series of prices to a day (the first day of a range). I'm doing this to numerous named ranges. The code that i build takes ages to do this:
1. Is there a faster way to adjust the size of the columns? I had to use my mouse to stretch the column out. This can be quiet time consuming if there are 8 columns needing to adjust to the same size. ie Col A, B,I, J needs to be the same size.
2. Can Excel determine the best size for the column? Say I need a column with data saying "yes" and "no" and a column for the letter "X" only or do I have to stretch the width of the column myself?
Any way that the following code could perform faster? Unfortunately, i'm deleting 35,000 rows of data quite frequently from a filtered list. Could be up to 35 columns wide.
Code: ls = Cells(Rows.Count, 1).End(xlUp).Row If ls > 1 Then Range("A2:A" & ls).SpecialCells(xlCellTypeVisible).EntireRow.Delete End If
I have the following code performing SumIf and searching roughly 9,000 rows of data. Calculation takes roughly 15 seconds on the high side.
Bonus question from a rookie - This code runs well in Sheet2, but errors out on the SumIf's when placed in a module or userform. What needs to be changed for it to work outside of Sheet2?
Private Sub DistNeg_Click()
Dim BotRow As Long
Application.ScreenUpdating = False Worksheets("Sheet2").Range("A4:E65536").ClearContents 'Copy current selected Distributor names to sheet2 Worksheets(1).Range("DIST1").Copy Destination:=Worksheets(2).Range("A4") 'Define the bottom row of distributors in this list. BotRow = Worksheets(1).Range("DIST1").Rows.Count + 3 'Sum for PY, CY and $CHG sales. Create percentage Chg column. Worksheets(2).Range(Cells(4, 2), Cells(BotRow, 2)).FormulaR1C1 = "=SUMIF(Sheet1!
I am only looping it through about 4 thousand records, looking for match and dups and moving it over to the other sheet, so why is my code running so slow?
I have run other programs where the records are in 40 thousand and it doesn't take as long as it is right now. Is there a way to make code run faster?
I need to find row numbers of the horizontal page breaks on a sheet quickly. The only method I have found to do this is by using the HPageBreaks property, which is painfully slow.
Is there another way of doing this that's faster? Or some other manner of using HPageBreaks that improves its speed?
The scenario is that I have a macro which generates a list of items where each item is two rows long. If an item intersects a page break--one row is on one page and the 2nd row is on the next page--I want to insert a row so that the entire item is on the next page.
I have a macro which is able to run very fast in Excel 2000. Almost 5s. When i run it in Excel 2003, it takes almost 4 to 5 mins to complete. Is there any patch in 2003 i need to run the macro smoothly?
I have an Excel file with a table of data on Sheet1. The table of data has 3 fields which are Code (a Text field) , Date (a Date field) and Amt (a Number field). I'm using vba with sql code to query that table and return the results of the query to a different sheet in the same file. So just to reiterate, Excel is my data source. I've done my research and largely worked this code out.
I'm also plugging a date parameter into the WHERE CLAUSE of the vba/sql code and this date is drawn from cell D4. This way, the user can change the date in cell D4 and this will change the criteria in the WHERE clause of the sql query to give them the results they want.
However I am getting an error that reads 'Data type mismatch in criteria expression' when I run the below code, so there must be something wrong with the specific syntax I'm using to plug in the date from cell D4.
Note that I can get the code and parameter to work if I make the CODE field the parameter with the query definition line of :
query = "SELECT [Code], sum([Amt]) as Ttl FROM [Main$] WHERE [Code] = '" & Range("D4").Value & "' group by [Code]"
Sub Pull_Data_from_Excel_with_ADODB() Dim cnStr As String Dim rs As ADODB.Recordset
I have built an Application in Excel that is used by a userbase of 50+ people. Now, as standard, they are only able to access the document read only. The application writes data to an ".mdb" but the user cannot change the application directly istself.
Now, we've had the problem over the last few days that the program is running slowly when it is trying to write data back to the ".mdb" file.
I believe that it could be because connection to the sever that our application is running slowly (or the server is too highly trafficed) but our server team are telling me there is nothing wrong with the server and it's running slowly because there are too many people in one Excel application/
In my spreadsheet I load 26 CSV files. Each CSV file has 3 columns and 7 rows. It uses the connection query in Excel. It is only relatively slow to refresh the data. To refresh the data, I can press the refresh data button in Excel.
A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value, and another routine that compares that saved value to the current time and displays the difference. At the beginning of a section of code you want to analyze, you call the first routine (which saves the start time), and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.
Is it possible to take a list of numbers then match those numbers to another list of numbers as a whole? I'm trying to make changes to my code and take some of the advice from previous threads, but I can't visualize it. I'm trying to get away from matching each list of numbers to each other line by line. I have an existing sheet (Master) that has several col's of data. Then I have another worksheet that has updated info that I need combined into the master. What I've done so far is added the information to the bottom of my existing data and sorted.
Now I have setup an advanced filter to display the dupes. Here is where it gets tricky..I can't just delete dupes b/c there are cols to the right that have history that cannot be deleted. So I need to copy ranges A to J of the new data and replace the old data within that range.
Need running clock in Excel... in A1 I have put running clock which is taking current system time. However, I want to add running clocks for EST in B1 - CST in C1 and PST in D1.
VB code I used to display current system running clock
Global clockOn As Boolean Sub runClock() Range("A1").Value = Now() If clockOn = True Then
I have developed a model for a client which keeps crashing after being used a few times. The crash is major! Excel shuts down and asks to send an error report.
This can happen after being run anywhere from 3 to 20 times. (Clicking a POST button which transfers items to a "database" type sheet).
I developed the model on a PC using Excel 2010. When this was loaded on a Mac, the user tells me that it happened periodically, and I have seen is occasionally on my PC as well.
However, the workbook has now been loaded on a server, with users across the state of Victoria accessing via Sharepoint - most using Excel 2007. Now it is happening very frequently.
I don't think the macro is at fault, it doesn't go into debug. Excel just seems to GIVE UP.
Have you seen this kind of thing before? Is there a way of "flushing buffers" or something so that Excel can keep working?
I am reluctant to post the model because it is full of personnel details, and is 2.5mb.
