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?
View 4 RepliesI convert text file into Excel. Then what I want is to look in column C for cell value “UNT:” (it is chopped word account: ) and copy value what is in the same row next column D into column K.
Then in column K copy down the value until next not empty cell.
My problem is that it works on Excel 97 and 200 but 2003 doesn’t work.
I have constructed a macro, (with lots of help from the fine people on this site), and have distributed it to several people in the company. The macro was written in Excel 2003, and runs perfectly on all the machines running Excel 2003, but persons using Excel 2000 are getting a runtime error, variable not defined.
Is there a trick to getting a macro to run in both versions of Excel?
I'm trying to run a macro on my machine (Windows 2000 and Office 2003) and I keep getting an error on one method. Now I tried to run the same macro on a different machine (Windows XP and Office 2003) and it didn't error out.
Public strControlTitle As String
Public strControlTime As String
Public strControlUnit As String
Public intControlItem As Integer
Public intLastNameOnly As Integer
Public intDataType As Integer
Sub Main()
Dim strControlItem, strPageName, strValue, sigma, z95, z99 As String
Dim r, c, count, cmax, rmax, rt, ct, low, high, a, b As Integer
Dim lesscol As Integer
With ControlChartForm
.ComboBox1.AddItem ("Average Turn Around Time All Patients")....................
Excel 2000 spreadsheet everything is fine.
When I open the same file in excel 2003 #NAME? Error appears.
I check Accept Labels in Formulas and it helped in some places but not everywhere.
I still have lots of NAME errors in a relatively simple spreadsheet.
I Excel 2000 everything works fine.
I have an activity recording tool created in Excel 2003 which works perfectly (ish)
However when some of my colleagues use it on machines running Excel 2000 it falls over at the following definition
Dim dlgFile As FileDialog
this is part of the following sub routine
Sub doCopy()
Application.ScreenUpdating = False
Call Archive
Dim dlgFile As FileDialog
Dim rv As Long
Dim sCreated As String
Dim sClosed As String
Dim wsThis As Worksheet
Dim wbkAOM As Workbook
Dim sThisCol As String
Dim aAOMCol As String
Dim strDate As String
Dim wsCore As Worksheet
Dim wsDiverted As Worksheet
Dim iRow As Integer
Set wsThis = Application. ActiveWorkbook.ActiveSheet
Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
dlgFile.Title = "Select AOM Sheet"
dlgFile.InitialFileName = "*.xls"
rv = dlgFile.Show.......................
I have a Find Method routine in a procedure that works fine in xl2000, but fails in xl2003 with error code 9, subscript out of range.
Is there a different format for the Find Method in xl2003?
'code in xl2000
Dim CourseI As Range 'Students taking BusinessI
Dim CourseII As Range 'Students taking BusinessII
Dim BothCoursesTrue As Range
Dim Student As Range
'code
'code ...
For Each Student In CourseI
Set BothCoursesTrue = CourseII.Find(Student, LookIn:=xlValue, LookAt:=xlWhole)
If Not BothCoursesTrue Is Nothing Then Student.Offset(, 3) = 3
Next Student
I recently launched a model that uses filtering on protected worksheets. The model was developed in Excel 2000, and everything works perfectly for the users who are also on 2000, but my Excel 2003 users get an error on opening the workbook.
The error asks for the password to unlock the sheet and is difficult to clear. Users have to hit Cancel 8 times to clear the error, at which point, the model appears to function perfectly. (There are 12 worksheets in the model, 2 with filters, all locked and password protected.)
The follwoing code is in the 'ThisWorkbook' module:
Private Sub Workbook_Open()
With Worksheets("Initiatives")
.Protect DrawingObjects:=True, contents:=True, userInterfaceOnly:=True
.EnableAutoFilter = True
.Protect contents:=True, userInterfaceOnly:=True
End With
Because the error occurs when first opening the model but not at other times, I'm thinking that there's something in the Workbook_Open procedure that is causing the error.
When I open an excel 2000 spreadsheet in 2003, the formulas fields do not populate data just #N/A. I can see data just fine in Excel 2003 viewer.
View 5 Replies View RelatedI am transferring 10000 rows from 1 workbook to another..
It takes place smoothly for the first 30000 rows, but after that on the 'select' line it gives me an overflow error.
The code that I am using is as following: .....
Here is exactly what was described:
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.
This sounds like a great tool for optimization...
To speed up copy and paste operations in a macro, I've heard that one can replace the following:
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1").pasteSpecial
with
Sheet2.Range("B1:B200").Value= Sheet1.Range("A1:A200").Value
The second code should run faster as the clipboard is not used to store the copied info.
When I tried this, though, I didn't notice any improvement in macro running speed.
I've developed a workbook with macros in Excel 2007 (Compatibility
Mode), Windows Vista Operating System. The purpose of the Macos
is to allow auto complete on a validated data entry field.
The macros run fine on this platform. I then copied the workbook
to my desktop, Excel 2000, Windows 2000 Professional Operating
System. One of the macros is killing Excel on this platform.
the macro works fine until it executes the paste values. At that point, the macro jumps to the "CountThem" function which is located in another workbook. The data that I am copy/pasting is in no way connected to any cells that are using that function. Although, other values in the workbook are passed down from data that uses that function.
I am still in the dark ages using Excel 2000.
This is the code for my macro.
Code:
Sub Current_to_Raw()
'
' Current_to_Raw Macro
' Macro recorded 2/12/2014 by
'
'
Range("N14").Select
[code]....
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..
View 7 Replies View RelatedIs there a faster (maybe a non-loop) way of doing this?
Private Sub Worksheet_Activate()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Test1 As Single
Dim Test2 As Single
Dim Test3 As Single
With Sheet2
For Test1 = 33 To 52
If .Range("J" & Test1).Value = "End" Then
.Rows(Test1).Hidden = True
Else: .Rows(Test1).Hidden = False
End If
Next Test1
This causes a bit of a delay (7 seconds or so) when switching to the sheet, which is causing the user to think that excel has locked up.
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
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.
way to bring more efficiency in this code.
I need to run this code on 100,000 rows every week and this takes hours to finish.
Is there a way to count if there are 5 or more in a range without
using 'select case'
or not using the 'intcounter' bit bit of code?
Set Startcell = Sheets("meetingstodate").Cells(xlrow, 2)
Set Endcell = Sheets("meetingstodate").Cells(xlrow, 7)
Set rng = Range(Startcell, Endcell)
lastrow = Sheets("meetingstodate").Cells(xlrow, 2).End(xlDown).Row
For xlrow = 2 To lastrow
arr = Range(Startcell, Endcell).Value
For j = 1 To UBound(arr)
For k = 1 To UBound(arr, 2)
Select Case arr(j, k)
Case A
intcounter1 = 1
Case B .....................
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!
View 9 Replies View RelatedMy 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.....................................
Instead of doing a long long formula that is =VLOOKUP(x1,....)+VLOOKUP(x2,.....) is there a faster way of doing it in the one formula?
View 7 Replies View RelatedI 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.
View 14 Replies View RelatedI'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:
View 9 Replies View Related 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?