Excel2000: code too slow

[Arvi Laanemets]

Hi

The code snippet below works too slow. It exports data from one Excel table
(Customer,CustCode, Article, January, ..., December) to new one in different
workbook (CustCode, Article, FirstWorkdayOfMonth, EntryType, CustCode). It
looks like the problem is writing data to new table - writing 12 months data
(12*5 fields) from a row in source table into new table takes ~50 sec. As
the source table has almost 2000 rows, and most of rows have values for all
12 months, it takes >10 hours to generate the new table. (The rest of code
takes next to nothing to run - the code row displaying process info on
status bar was added to check this)

At start I used offset to determine target cells, and suspected this to be
the cause. So I replaced offsets with direct references - but this had no
effect at all.

Can anyone explain me this? Thanks in advance!

....
k = 0
....
If
Workbooks(varSource).Sheets(varSheet).Range("A1").Offset(i - 1, 1) = varCode
Then
varArticle =
Workbooks(varSource).Sheets(varSheet).Range("A1").Offset(i - 1, 2)
For j = 1 To 12
If
Workbooks(varSource).Sheets(varSheet).Range("A1").Offset(i - 1, j + 2) > 0
Then
Application.StatusBar = "Code: " & varCode & ",
Article: " & varArticle & ", Month:" & arrMonths(j)

ThisWorkbook.Sheets("Forecast").Range("A" & (k +
1) & ":C" & (k + 1)).NumberFormat = "@"
ThisWorkbook.Sheets("Forecast").Range("D" & (k +
1) & ":E" & (k + 1)).NumberFormat = "General"
ThisWorkbook.Sheets("Forecast").Range("F" & (k +
1)).NumberFormat = "@"
ThisWorkbook.Sheets("Forecast").Range("A" & (k +
1)) = varCode
ThisWorkbook.Sheets("Forecast").Range("B" & (k +
1)) = varArticle
ThisWorkbook.Sheets("Forecast").Range("C" & (k +
1)) = arrMonths(j)
ThisWorkbook.Sheets("Forecast").Range("D" & (k +
1)) = Workbooks(varSource).Sheets(varSheet).Range("A1").Offset(i - 1, j + 2)
ThisWorkbook.Sheets("Forecast").Range("E" & (k +
1)) = 4
ThisWorkbook.Sheets("Forecast").Range("F" & (k +
1)) = varCode
k = k + 1
End If
Next j
End If
Exit For
End If
....

 

[Guest]

Arvi,
I emulated your code by simply selecting 2000 records (to
generate 24000) and it took less than a minute! So maybe my emulation was not
realistic but I cannot understand why it would hours (never mind 10 minutes).

Unless there is reason not to do so, you could format the columns prior to
insert the data rather than in the loop - this speeds things up.

I have Excel 2003 but I don't believe this makes any (significant)
difference over 2000.


If you have file you can send me with all the code, I would willing to test
further.

([email protected],.uk)

 

[Arvi Laanemets]

Hi

Your response did give me an idea to test the source workbook. I copied
cource data to new workbook, and imorted from there - it worked like charm.
But I'm really confused - the delay was in part of code after
Application.Statusbar... row, and there is only one statement reading a
value from source table. A plain value - not a formula! At same time in code
I posted I skipped several statements where other data from same source
table were read - and those statements worked without any delay.
PS. The source workbook contains a couple of pivot tables based on same
table as my source table. But I can't see how this can influence the reading
of data from their common source table!

But the case is closed now anyway - and I thank you!