| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Worksheet Functions | |||
| Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Excel Discussion (Misc queries) | |||
| Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting | Links and Linking in Excel | |||
| Lookup / Loop / Optimization Help Requested | Excel Programming | |||
| Loop removal or optimization | Excel Programming | |||
Linear Mode
