Need Loop Optimization Assistance
 

The code I have below steps through a VBA resultset inserting specific values
into a range of cells, one by one. Clearly, a repetitive single insertion is
slow; however, I'm not familiar enough with VBA to optimize a resultset based
query with something faster. Can anyone provide some additional help?

eventresults.MoveFirst
Dim i As Integer
i = 2
While Not eventresults.EOF
Sheets("Raw").Cells(i, 1) = eventresults.Fields(0).Value
Sheets("Raw").Cells(i, 2) = eventresults.Fields(1).Value
Sheets("Raw").Cells(i, 3) = eventresults.Fields(2).Value
Sheets("Raw").Cells(i, 4) = eventresults.Fields(3).Value
Sheets("Raw").Cells(i, 5) = eventresults.Fields(4).Value
Sheets("Raw").Cells(i, 6) = eventresults.Fields(5).Value
Sheets("Raw").Cells(i, 7) = (Sheets("Raw").Cells(i, 5) -
Sheets("Raw").Cells(i, 6))

i = i + 1
eventresults.MoveNext
Wend

Need Loop Optimization Assistance
 

Try using the CopyFromRecordSet method of the Range object.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Chris C" wrote in message
...
The code I have below steps through a VBA resultset inserting specific
values
into a range of cells, one by one. Clearly, a repetitive single insertion
is
slow; however, I'm not familiar enough with VBA to optimize a resultset
based
query with something faster. Can anyone provide some additional help?

eventresults.MoveFirst
Dim i As Integer
i = 2
While Not eventresults.EOF
Sheets("Raw").Cells(i, 1) = eventresults.Fields(0).Value
Sheets("Raw").Cells(i, 2) = eventresults.Fields(1).Value
Sheets("Raw").Cells(i, 3) = eventresults.Fields(2).Value
Sheets("Raw").Cells(i, 4) = eventresults.Fields(3).Value
Sheets("Raw").Cells(i, 5) = eventresults.Fields(4).Value
Sheets("Raw").Cells(i, 6) = eventresults.Fields(5).Value
Sheets("Raw").Cells(i, 7) = (Sheets("Raw").Cells(i, 5) -
Sheets("Raw").Cells(i, 6))

i = i + 1
eventresults.MoveNext
Wend

Need Loop Optimization Assistance
 

That worked. Thanks.

"Charles Williams" wrote:

Try using the CopyFromRecordSet method of the Range object.


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"Chris C" wrote in message
...
The code I have below steps through a VBA resultset inserting specific
values
into a range of cells, one by one. Clearly, a repetitive single insertion
is
slow; however, I'm not familiar enough with VBA to optimize a resultset
based
query with something faster. Can anyone provide some additional help?

eventresults.MoveFirst
Dim i As Integer
i = 2
While Not eventresults.EOF
Sheets("Raw").Cells(i, 1) = eventresults.Fields(0).Value
Sheets("Raw").Cells(i, 2) = eventresults.Fields(1).Value
Sheets("Raw").Cells(i, 3) = eventresults.Fields(2).Value
Sheets("Raw").Cells(i, 4) = eventresults.Fields(3).Value
Sheets("Raw").Cells(i, 5) = eventresults.Fields(4).Value
Sheets("Raw").Cells(i, 6) = eventresults.Fields(5).Value
Sheets("Raw").Cells(i, 7) = (Sheets("Raw").Cells(i, 5) -
Sheets("Raw").Cells(i, 6))

i = i + 1
eventresults.MoveNext
Wend