Hi,
I have a 2 dimension collection. The collections has 7 rows and 25 columns.
Writing this collection to a sheet takes about 60 seconds, which is slow to me.
The calculations are set to manual, and the displays, events and screenupdaing are set to false.
What else could be causing the code to run slow. The excel file is pretty busy with a lot of sheets and pivot tables (12.777 kb). Could the size of the file cause the code to run slow?
Any ideas?
I have a 2 dimension collection. The collections has 7 rows and 25 columns.
Writing this collection to a sheet takes about 60 seconds, which is slow to me.
The calculations are set to manual, and the displays, events and screenupdaing are set to false.
What else could be causing the code to run slow. The excel file is pretty busy with a lot of sheets and pivot tables (12.777 kb). Could the size of the file cause the code to run slow?
Code:
SECS1 = Timer()
For i = 1 To theCollection.COUNT
rowNumber = i + 10
With Sheet17
.Cells(rowNumber, 1) = theCollection.item(i).debtType
.Cells(rowNumber, 2) = theCollection.item(i).account
.Cells(rowNumber, 3) = theCollection.item(i).companyCode
.Cells(rowNumber, 4) = theCollection.item(i).investment
.Cells(rowNumber, 5) = theCollection.item(i).scheduleContact
.Cells(rowNumber, 6) = theCollection.item(i).loanDescriptions
.Cells(rowNumber, 7) = theCollection.item(i).maturityDate
.Cells(rowNumber, 8) = theCollection.item(i).currency_
.Cells(rowNumber, 9) = theCollection.item(i).interestRates
.Cells(rowNumber, 10) = theCollection.item(i).InterestExpense
.Cells(rowNumber, 11) = theCollection.item(i).interestPaid
.Cells(rowNumber, 12) = theCollection.item(i).M110
.Cells(rowNumber, 13) = theCollection.item(i).M210
.Cells(rowNumber, 14) = theCollection.item(i).M220
.Cells(rowNumber, 15) = theCollection.item(i).M225
.Cells(rowNumber, 16) = theCollection.item(i).M310
.Cells(rowNumber, 17) = theCollection.item(i).M350
.Cells(rowNumber, 18) = theCollection.item(i).M500
.Cells(rowNumber, 19) = theCollection.item(i).M510
.Cells(rowNumber, 20) = theCollection.item(i).M520
.Cells(rowNumber, 21) = theCollection.item(i).M521
.Cells(rowNumber, 22) = theCollection.item(i).M530
.Cells(rowNumber, 23) = theCollection.item(i).M540
.Cells(rowNumber, 24) = theCollection.item(i).M541
.Cells(rowNumber, 25) = theCollection.item(i).M799
End With
Next i
SECS2 = Timer()
Debug.Print SECS2 - SECS1
Any ideas?