Optimize Calculation Speed

Mar 24, 2009

I have a rather large spreadsheet that takes a very long time to calculate once the new data is added. One of the many things I need to do is look to see if a unique value in range 1 is also in range 2. If it is, return some data (vlookup), if it's not, then I want a "0", not #NA. My question is, which of these two methods will result in a faster calculation (if at all):

Option 1: Do it in one step
=IF(ISNA(VLOOKUP($A3,LY,3,FALSE)),0,VLOOKUP($A3,LY,3,FALSE))

Or option 2: Do it in two steps:
Column N formula:
=VLOOKUP($A3,LY,3,FALSE)

Column N+1 formula:
=IF(ISNA(Column N value),0,=column N value)

I have many columns of data using formulas in option 1, so if I have coded this badly that could be my problem...

View 9 Replies


ADVERTISEMENT

Speed-Up/Optimize Recorded Macro Code

Aug 30, 2006

Im setting up a spreadsheet that does engineering calculations. Im using macros to run sizes from a standard schedule. It basically takes the values from one sheet (schedule) to another (calculation), then the result from the calculation sheet (Value only, not the link) is pasted back into the schedule. The macro seems very bulky and im sure that it can be made more efficient with a loop. here is a sample of the code from the macro;

Sheets("Calc sheet").Select
Range("C6").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[1]C"
Range("C7").Select
ActiveCell.FormulaR1C1 = "=Schedule!RC[1]"
Range("C8").Select
ActiveCell.FormulaR1C1 = "=Schedule!R[-1]C[2]"
Range("C9").Select
Sheets("Schedule").Select
Range("G7").Select..................

View 3 Replies View Related

Calculation/ Macro Speed

Sep 24, 2008

I am having a big problem with recalculating cells after running a macro. The macro runs at a great speed, but once the calculation is switched back to automatic, the cells are not calculating fast enough. I waited for 4 minutes and it still had not moved past 0%. The worksheet has approximately 9000 rows out to IG columns. 85% of the cells contain an if-then formula.

View 9 Replies View Related

Speed Up Sheet Calculation

May 1, 2009

I have 40000 rows filling with data from column a to g, autofilter is alway on on said columns , the problem is that when I select any column to filter data as per my query , it takes long time to display result and sometimes it hangs with the msg xyz workbook is not responding.

View 9 Replies View Related

Calculation Update Speed

Jan 17, 2007

I have two spreadsheets 1 of which gathers data on individuals for a daily basis for the duration of 1 month via a database query. The other sources this data (not via a lookup) for 20 staff members of staff and details this in a report. The report allows staff members names to change and automatically update their information from the previous sheet.

My problem is that even though the file size is 694kb when asked to update the sheet takes an age to load. Is there anything I can do with my sheet?

View 9 Replies View Related

VBA - Change Calculation Settings Of Workbook To Speed Up Macro

Sep 29, 2011

I have a range of cells ( C2, C5, C8:N1007, P8:P1007 ....) on a worksheet that I want to be able to clear the contents of through a macro - worksheet is entitled Database

I have 3 additional worksheets all of which have summary information on and some of the formulas are huge.

I've inserted a module and produced this code;

Sub ClearContents()
Application.Calculation = xlCalculationManual
Range("C2, C5, C8:N1007, P8:P1007, ....").ClearContents
Application.Calculation = xlCalculationAutomatic
End Sub

Because of all the 3 summary page formulas the code is taking a few seconds to run.

I'm new to VBA and just wondered whether I could switch the calculation setting for the entire workbook onto manual at the beginning of the code and then switch it back to automatic at the end of the code? Think this might speed things up.

View 5 Replies View Related

Optimize Cut Lengths

May 20, 2009

I have a given length of steel bar...Example(550 feet)

I have various cut lengths that I can pick from...
14'-10".....13'- 9".. 12'-8"........(up to 6 different lengths)

I need to know the best combination of lengths to cut the long bar into to have minimal scrap left when we get to the end of the 550 feet.....

Cut as many of the longest cut lengths possiable.....Cut as few of the shortest cut lengths possiable

Due to the fact that I will have to convert this to a PLC application when completed, I want to find a mathmatical way to do this inside excel and not use the "solver"....Assuming that I/we can come up with an equation to do this .....

View 14 Replies View Related

Optimize Restructuring Macro

Apr 29, 2008

This macro accepts analytical files of constant format, imported into individual worksheets. Each file contains header information, followed by bulk analyses. Header information is extracted and used to tag individual records within an array. A unique header is created for each analytical method and erroneous values are treated. The array is output to a new worksheet, then the macro loops, repopulates the array, attempts to match the headers of the current array with those in the output worksheet and writes records in the corresponding column.

I'm particularly concerned with the section entitled "Match columns to headers" as this involves several nested loops and seems very inefficient.

As it goes, it works. However, as I intend to develop the macro further into something more versatile, it seems sensible to get the fundamentals correct.

Sub Format_Assays()

Dim batchId As String, inArray() As Variant, i As Integer, j As Integer, outRow As Integer, outCol As Integer, outSheet As Worksheet, sExist As Boolean, wSheet As Worksheet, x As Integer, xout As Integer, y As Integer, yout As Integer

'Cycle through required sheets
For Each wSheet In ActiveWorkbook.Sheets
If wSheet. Name = "Assays_All" Then
sExist = True
Exit For
End If
Next wSheet

View 9 Replies View Related

Optimize VBA Loop For Inserting Formula

Aug 4, 2009

I have a spreadsheet with 10,000 rows. In column A dates are mentioned and in column B I'd like to calculate the specific Quarter via VBA code as I can't have formula's in my sheet.
My VBA code is:

Sub QuarterCalc()
Dim myCell As Range
Application. ScreenUpdating = False
For Each myCell In ThisWorkbook.Sheets("Data").Range(Cells(1, 2), Cells(10000, 2))
myCell.Formula = "=INT((MONTH(RC[-1])-1)/3)+1"
myCell.Offset(0, 0) = myCell.Offset(0, 0).Value
Next
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

Optimize Slow Conditional Formatting Code

Jan 23, 2008

I am having a little trouble with this code, which runs in my simple but efective ressource overview. It loops through a rather large range and assigns interior colour to the cells based on certain criteria. On my stationary machine (Excel 2003) it takes approximately 15-30 seconds to run the code which is acceptable. On my laptop (Excel 2007) it takes 5+ minutes which is unacceptable. Is there a workaround so as to optimize the speed? Further, when I run this workbook on Excel 2007, even entering an integer in the sheet takes 3-4 seconds, and no code is running! In my first version I used a Change_Event to colour cells on the fly but this was slow and prevented multible cell editing as well as pasting values into the appropriate range.

Public Sub Farvelade()
Dim icolor, Navn As Integer
Dim TargetRow, LastRow, Previous As Long
Dim Target As Range
MsgBox "Det kan tage 15-30 sekunder at opdatere ressourcekalenderen"
Range("A5").Activate
Range("A5"). CurrentRegion.Select
'Set range
ActiveCell.Offset(Selection.Rows.Count, 0).Activate
LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row.....................

View 8 Replies View Related

Optimize Select Random Cell Value Without Duplicates Code

Apr 6, 2013

I have two worksheets. ws1 contains a large number of dates on column 3. ws3 contains around 20 dates on column 3.

I want to pick two dates randomly in ws1 that does not already exist in ws3.

Here is my working code, but it is really slow to process. How to optimize this code? Also I would like to add a function where if the macro is not able to find two random dates that aren't already in ws3 then exit loop.

VB:
Set ws1 = thisworkbook.sheets(1)
Set ws3 = thisworkbook.sheets(3)

lr = ws2.Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
Set rvis = ws2.Range("c2:c" & lr).SpecialCells(xlCellTypeVisible)
mynodate = 0

[Code] .....

View 7 Replies View Related

Optimize Slow Code And Avoid Copy To Clipboard?

Oct 7, 2013

this code to minimize it and avoid copy to clipboard.

VB:
Sub SapOutputRun()
Application.EnableEvents = False
Application.ScreenUpdating = False

[Code].....

View 2 Replies View Related

How To Speed Up Code

Aug 8, 2009

I'm reading in a text file with stocks data in the following format:

JRV,Jervois SUM,D,20090807,000000,0.00600,0.00600,0.00500,0.00600,41370,0
JYC,Joyce SUM,D,19900102,000000,1.25410,1.25410,1.25410,1.25410,0,0
etc...

which then compares the three-letter code with a list in the following
format to create a new text file where data is only included if the code
is on the list. Since I added the range search to the code it runs VERY slow

AACAust A Foo
AAFAustral AfMat
AAMA1 MineralMat
AARAnglo AustMat
AAXAusenco LiCap
ABBAbb Grain Foo
ABCAdelaide BMat
etc...

View 9 Replies View Related

How To Speed Up Autofiltering

Feb 26, 2006

How to speed up Autofiltering? Excel 2003 sp2 hangs up for 10 minutes after
I choose a record in the Autofilter.

View 13 Replies View Related

UDF Recalculation And Speed

Dec 11, 2006

I'm working on a financial reporting project that should be in Access but unfortunately it must be in Excel. Some of the formula are complex and I have a UDF to calculate these values. I added the line

Application.Volatile

to each UDF but when I change the current month in a dropdown box, the UDF's do not recalculate. The dropdown box sets a period number on one of the worksheets - this same value is passed to each UDF. I tried using this code in my dropdown box :


Sub DropDown4_Change()

Application.CalculateFull

End Sub
but the PC just hangs. I have hundreds (more likely thousands) of formula in the spreadsheet and the recalc is recalcing everything whereas I just want it to recalc the UDF's. I even changed all of the sumproduct formulae to array sum if formulae which sped things up - that is until I forced the full recalc on the drop down change event.

So my question is: is it possible to just recalc the UDF's on 3 worksheets when the user selects a different period in a dropdown box?

And a supplementary question : if {sum(if(...))} formula are faster than sumproduct formula, would a (well written) UDF perform faster than a {sum(if(...))} formula?

View 9 Replies View Related

How Can I Speed Up My Loop

Mar 4, 2010

I will attach a sample of the data when I figure out how to. The original data takes about 8 or 9 second to delete the lines.

Code: ...

View 9 Replies View Related

Increase Speed

Jan 15, 2007

to speeding up this little routine that deletes rows that have identical values in certain cells in the row above

Sub remo() ...

View 3 Replies View Related

Speed Up Loop

Apr 27, 2007

I required to do some Monte Carlo analysis for 1000000 simulation. I have managed to find some free code, however, the time it took to run 1000000 >30min. Is that normal? The code that it took the longest to run is following:

For i = 1 To number_of_trials
Application.Calculate
For j = 1 To number_of_formulas
runs(j, i) = sel. Cells(1, 1 + j)
Next j
Next i

Is there any way I can implove this code to make it run faster? I have already tried Application. ScreenUpdating = False

View 5 Replies View Related

Average Speed Of Competitors

Oct 11, 2013

I have a time (1:08:31) that it took to travel 35km. How can I calculate the average speed of this competitors plus a number of others who recorded faster or slower time?

I managed to convert the time to seconds but when I load a simple formula to convert to KPH it never works.

View 2 Replies View Related

Speed Up Clearcontents Macro

Nov 4, 2008

The macro clears specific columns in a row when you click anywhere on the row and then hit the command button. It clears the first range and 2nd range in 2 distinct steps, and takes up to 3 seconds.

View 5 Replies View Related

Speed/Efficiency For If-Else Construct

Apr 21, 2009

My question is about the If-Else Construct.

I often write If-Else statements that require an action be taken only if something is true. If that something is false, no action is to be taken.

My question is, how do you code "no action".

The following is what i usually

View 5 Replies View Related

Trying To Calculate (Time To Top Speed)

Feb 17, 2013

I have 3 numbers:

1) Current Speed
2) Current Acceleration
3) Acceleration Growth

Assuming:
Current Speed=0
Current Acceleration = 0.2 (each 'turn' the current speed will increase by this much)
Acceleration Growth = 0.2 (each 'turn', the current acceleration will grow by this much)

This gives a current speed over a series of 'turns' as
0.0 + 0.2 = 0.2
0.2 + 0.4 = 0.6
0.6 + 0.8 = 1.4
1.4 + 1.0 = 2.4
2.4 + 1.2 = 3.6
3.6 + 1.4 = 5.0
5.0 + 1.6 = 6.6
6.6 + 1.8 = 8.4
8.4 + 2.0 = 10.4
etc.

What I'd like to do is have a formula (or some way other than calculating each step) to tell me how many turns it would take for the Current Speed to =>X (example 100)

Basically, Turns to X speed = something clever * acceleration growth * something else very clever.

View 9 Replies View Related

Speed Up Shared Workbook?

Aug 19, 2013

how to speed up a shared excel worksheet?

View 4 Replies View Related

Permutations Checkspelling Speed

Mar 21, 2007

A1 has some characters
this code will generate all possible words, that can be made using all characters

system
permutate and checkspelling: if OK then write to column B

example
A1: iftrs
results: first frits rifts

Option Explicit


Dim CurrentRow
Const col = 2

Sub correctly_spelled_permutations()
Dim InString As String
Dim CalcSet As Integer
InString = Range("A1")
If Len(InString) < 2 Then Exit Sub

With Application
.ScreenUpdating = False
CalcSet = .Calculation
.Calculation = xlCalculationManual
.EnableCancelKey = xlErrorHandler
.StatusBar = "searching valid combination"....................

View 9 Replies View Related

Speed Up Slow Macros?

Apr 4, 2007

I use the Application.ScreenUpdating = False all the time. Any there any other things like this which speed up macros?

View 9 Replies View Related

Speed Up Frame1.Clear

Aug 16, 2007

I have a Frame on a UserForm. The Width of the frame depends on values given in TextBox.Dat1 and TextBox.Dat2.

Now I tried it with a width of 2200 with a scroll bar, but when I change the value in the Textboxes I need the Frame to be cleared. Now this is my question. I use "UserForm1.Frame1.Clear" but this can take up to 1 minute.

Is there anyway to speed this up?

View 9 Replies View Related

Increase Speed And Simplify VBA

Oct 15, 2007

I'm trying to automate a process where I get a list of checks and money orders purchased then manipulate it to make auditors happy. I need to eliminate all transactions less than $3000. The fly in the ointment, however, is NOT to eliminate daily transactions by the same purchaser that may be less than $3000, but when added together are greater than $3000.

I've created a helper column where I've inserted the formula ....

View 10 Replies View Related

How To Speed Up Select Case

Dec 2, 2007

i hv a excel file which have 200k row. recently i learn from this forum using VBA (excel macro code) for select some data for analysis. it take very long. any suggestion will be apprecaited.

i already increase my note book ram to 2GB. it still slow.

View 9 Replies View Related

Shortening A Speed Sheet

Apr 18, 2008

weekly i have a report that is generated that is over 5000 lines and 4 columns wide.

I currently copy and paste one page column to make it 16 columns across the page. Is there a function in excel to do this?

View 9 Replies View Related

How To Convert/speed Up This Formula

Oct 31, 2008

The formula: ={IF(AND(ISNUMBER($A3);($A3-DAY($A3)+1)=F$2);$D3;IF(AND(F$2 > ($B3-DAY($B3));F$2 < DATE(YEAR($C3);MONTH($C3)+1;0));$D3/DATEDIF($B3-DAY($B3);DATE(YEAR($C3);MONTH($C3)+1;1);"m");0))}

I need to use this formula for over more than 30.000 rows and more than 50 columns. Is it possible to speed up the formula? Or maybe to handle this by a macro?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved