How to speed up this code

[Surreall]

Hi all,

I am having problems speeding up code. Cause of the size of the spreadsheet it is taking several minutes to run, and i need to get this down to just a few seconds. I have upgraded to 2007 to utilise multithreading which halfed the time ish, but still not nearly quick enough.

The code that is taking for ever is as follows,

It iterates 39 times (i have 39 worksheets), this is the code that works on each sheets

Please Login or Register  to view this content.

This is also using calculation = false and updating screen false

So it goes through each cell to check. "D" is several thousand, so lots and lots of iterations

Is there a way to use arrays or even Ranges to save time?

Thanks for your help a very frustrated beginner programmer

Rgds

Surreall

The full code for those interested

Please Login or Register  to view this content.

[HaHoBe]

Hi, Surreall,

have you tried running the code with this line commented out:

Please Login or Register  to view this content.

Ciao,
Holger

[Surreall]

Thank you for your response.

Yes i tried without that, didnt make a difference

Rgds

Surreall

PS i think i need to set a whole range, ie from Cells(2, a + 1) to Cells(t, D) and put the formula to the range. But i have no idea how. If i could do that then instead of going through each cell it would do just one iteration per sheet

[HaHoBe]

Hi, Surreall,

i think i need to set a whole range, ie from Cells(2, a + 1) to Cells(t, D) and put the formula to the range.

Sorry but I canīt find a formula being inserted (maybe should go out for some new glasses ).

Without testing (only avoiding all Selections made):

Please Login or Register  to view this content.

Ciao,
Holger

[Surreall]

Moderator's Note: As per Forum Rule #12, please don't quote whole posts unless necessary…it's just clutter...Thanks.

Thankyou for your effort, greatly appreciated i will give it a go. Just one question what does putting a "." in front of cells do? Ie x = .Cells(a,b) instead of x = Cells(a,b)?

Rgds

Surreall

[HaHoBe]

Hi, Surreall,

regarding yuur question: in a With-Construct where a certain Sheet is referred to the .Cells(4, 3) are on the wanted (must not be the active) Sheet whereelse Cells(4, 3) will always relate oi the ActiveSheet.

Please Login or Register  to view this content.

should display the contents of Cell C4 of Sheet Test - no matter if that Sheet is active or not.

Please Login or Register  to view this content.

will only display the wanted value if the ActiveSheet is Test (just to make sure: this worksheet needs to be in the active Workbook or the name of the workbook must be added in front).

Please do not quote full posts according to Rule #12.

Cia,
Holger

[Surreall]

i will refrain from quoting full posts in the future.

The code is much smoother now, and quicker, but still takes several minutes to run.

What about replacing all those For's and ifs with one statement using range? Is that possible? Or using arrays? (i really cannot get my head around arrays)

Rgds

Surreall

[HaHoBe]

Hi, Surreall,

could you please explain the line

Please Login or Register  to view this content.

If you want only every second column to be checked you could use

Please Login or Register  to view this content.

Due to some performance problems (using Excel2000 then) I started building a range and using For Each rngCell in myRange instead of utilizing 2 loops. The For Each will only work on one row with all the columns and then go back and start with the next row.

Iīm not sure about other measures being taken as I have no idea of what is checked in which way (or if maybe some filter might be used for that).

Ciao,
Holger

[jeffreybrown]

@Surreall,

Moderator's Note: As per Forum Rule #12, please don't quote whole posts unless necessary…it's just clutter...Thanks.

[Surreall]

@ jeffreybrown, noted

HaHoBe,

The a + 1 is so that it goes to the next cell across.

so it does the check in this order (although the order doesnt matter)

Range A1 then B1 then C1 (next t)
A2, b2, c2 (next t)
a3, b3, c3 etc

I wrote another similar script, but just plugged in the formula into each cell. This enabled me to write the formula the whole range on each sheet once.
The problem with that though, is instead of each cell containing a 1 or 0. Every cell contained the big if's statements, so the size of the spreadsheet quadrupled.

Rgds

Surreall