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
ADVERTISEMENT
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
Jul 20, 2006
how to speed up the following loop? It takes about 40 seconds.
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Integer
Dim y As Integer
Dim Snominaloc As Double
Dim Scs As Double
Dim Sirating As String
Dim Mnominaloc As Range
Dim Mcs As Range
Dim Mirating As Range
Dim Mcbrating As Range
Set Mnominaloc = Worksheets("Parameters").Range("b10")
Set Mcs = Worksheets("Parameters").Range("b22")
Set Mirating = Worksheets("Parameters").Range("b12")
Set Mcbrating = Worksheets("EL").Range("b33")......................
View 2 Replies
View Related
Aug 18, 2007
I have a large worksheet (Sheet1) containing approximately 15,000 records (15,000 rows x 21 columns). I need to search through all these records, and manually decide whether a record should be copied onto another worksheet (Sheet2) or not. The code I have written works very well (as far as I can tell), but it is extremely slow (the searching takes forever).
The code is as follows:
Sub SearchAndCopy()
Dim SearchRow As Long
Dim LastRow As Long
Dim SearchColumn As Long
Dim LastColumn As Long
Dim CopyToRow As Long
Dim SearchString As String
Dim Found As Boolean
Dim Response As Integer
On Error Goto Err_Execute
Found = False
'Fetch Search Term
Sheets("Sheet2").Select
SearchString = LCase( Range("F3").Text)......................
View 3 Replies
View Related
May 14, 2014
I have a macro I run on thousands of rows of data which occasionally has incorrect line breaks that need to be brought up to the previous row. The macro works perfectly, however I'm trying to speed it up by skipping rows that do not need processing.
The macro inserts a check in column AQ to see if column A meets certain criteria. If it does not, "False" is entered in AQ, indicating the need for processing. As it runs now, the loop steps through each row of AQ to see if it needs further action.
I am trying to come up with a way for it to automatically just jump to "False" instances, skipping sometimes thousands of rows of blank cells that need no processing.
[Code] ......
View 12 Replies
View Related
Feb 21, 2010
I have a problem with one of my loops, it takes about 17 seconds to do the job of calculating a simple moving average for 200 periods on 20,000 rows. However, if I do the "FillDown" function for the same type of average, it takes 1 second.
Here is the code for the loop:
View 9 Replies
View Related
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
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
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
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
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
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
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
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
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
Aug 19, 2013
how to speed up a shared excel worksheet?
View 4 Replies
View Related
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
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
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
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
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
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
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
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
Nov 20, 2008
I currently have a macro set up to delete rows if a certain user selected value is not found in a certain column. It works fine if the sheet isn't overly large but the problem is we have some spreadsheets with 25,000+ rows and it takes time to loop through - I'm not sure if there is even a way to make it faster.
Currently it works by looping backwards on the sheet checking each value in the cell versus an array containing the user selected values. If a match isnt found it deletes this row.
'y = long value representing row
'x = counter for each item in user selected array
'wsSheet = worksheet we are using
'rowLast = last row on spreadsheet
'arrSearch() = string containing user selected items
i have screenupdating/calculations/events turned off - i just didnt know if there was a faster way of looping through
i just thought about instead of doing an array maybe joining the array into 1 string and using a "like" comparison check to see if a match is found - would save me from having to constantly loop x * y amount of times - not sure if this would work or not. ill post back if it doesn't
View 9 Replies
View Related
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
View Related
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
Sep 4, 2009
I have this analyze that is runned by a macro in one workbook, and it starts a analyzing-process in another workbook. The data is picked up in the no2 book, and returned to the first book. It is analyzing lots of workbooks, sometimes up to 1000 workbooks, it means that no2 workbook gets a new name and then saved.
I have once heard that the process could be way faster if the workbooks where the analyzes is processed through not were saved, and I actually dont need the books as long as I got the data into my first workbook.
But Im not sure what in the macro that makes it save the no2 workbook, but I would really like to speed up this process. As it is now I have to start the analyze before I go to bed, and the hopefully it's done when I wake up next morning.
View 9 Replies
View Related
Oct 30, 2009
when the Size is say 5000 Rows but when i have more records in my sheet excel just times out and ends with an Error.
Note: I am using the statement below within the for loop
Sheets("MergedDataSheetQ").Range(rplRange.Offset(0, 0), rplRange.Offset(sizeData, noColumns + 2)).AutoFilter Field:=noColumns + 1, Criteria1:=SubSector
Is there any work around so that i only change the criteria inside the loop or something like, so that the i reduce the execution time for this statement?
View 9 Replies
View Related
Mar 2, 2010
I wrote a macro that works fine, although it runs extremely slowly as if plodding along through all of the cells one at a time. I'm sure the computer is faster than that, so I would like it to chug through more quickly.
I am using some syntax that could be optimized ....
View 9 Replies
View Related
