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