Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimize VBA Excel 2003 NextFor loop
I'm using Excel 2003 VBA to copy and paste values from one cell to
another which my spreadsheet then uses to runs these values through many formulas and generates one result. VBA copies this result to a result column and proceeds to the next row and so on for about 1000 rows. VBA is simply being used to feed many combinations of values (in my case, pairs of values) into a 20MB spreadsheet which does the heavy calcs. I tested my code on 5 rows of input values and it took ~30 seconds to generate a result. Can anyone give me tips for optimizing my code? I have already done the following: (1) declared my variables as Range rather than Object and as Integer rather than Variant, (2) turned off screen updating, (3) used Range instead of Cells, (4) used Range Object instead of Selection Object, (5) set my range info as a variable rather than specifying . I read on Chip Pearson's website that For Each loops are usually faster than For...Next loops, but (as is obvious) I am new to VBA and do not know how to use For Each with my project. I also cannot turn off calculate because I need the spreadsheet to recalculate each time a new row is evaluated. Below is my code as it currently stands: Sub OpponentsOdds() Worksheets("Pairs").Activate Application.ScreenUpdating = False Dim I1 As Range Dim I2 As Range Dim I3 As Range Dim I4 As Range Dim Index As Range Dim Counter As Range Dim TotPairs As Range Dim Suit1 As Range Dim Suit2 As Range Dim Value1 As Range Dim Value2 As Range Dim IndexTot As Range Dim i As Integer Set I1 = Range("Input1") 'The following variables each refer to 1 cell Set I2 = Range("Input2") Set I3 = Range("Input3") Set I4 = Range("Input4") Set Index = Range("Index") Set Counter = Range("Counter") Set TotPairs = Range("TotPairs") Set Suit1 = Range("Suit1") 'The following variables refer to 2704 cells Set Suit2 = Range("Suit2") Set Value1 = Range("Value1") Set Value2 = Range("Value2") Set IndexTot = Range("IndexTot") 'Define loop by number of pairs (counter); use 5 for test purposes For i = 1 To 5 If Counter(i) < "" Then 'Copy and paste pair info to inputs Suit1(i).Copy I1.PasteSpecial Paste:=xlPasteValues Value1(i).Copy I2.PasteSpecial Paste:=xlPasteValues Suit2(i).Copy I3.PasteSpecial Paste:=xlPasteValues Value2(i).Copy I4.PasteSpecial Paste:=xlPasteValues 'Copy and paste Output (or index) to output column (IndexTot) Index.Copy IndexTot(i).PasteSpecial Paste:=xlPasteValues Else Exit For End If Next i Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Optimize VBA Excel 2003 NextFor loop
Hi,
you can cut out the copy/paste process between two ranges using the following syntax MyRange1.Value = MyRange2.Value This will speed up the code considerbly. Hth, Oli -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested If Loop Limitation for Excel 2003 | Excel Discussion (Misc queries) | |||
How do I optimize file size in excel 2007 workbooks with pivot tab | Excel Discussion (Misc queries) | |||
How do I optimize system settings to support Excel? | Setting up and Configuration of Excel | |||
Optimize SumProduct | Excel Discussion (Misc queries) | |||
How can I optimize this code? | Excel Programming |