VBA - Full Speed Code Problems

Hi All,

I have a reasonably long piece of code that conducts a lot of operations particularly on ranges.
In short, it loops through all worksheets in a workbook and runs a lot of evaluate("index(functions))") on the .areas of a range object. This range object is made up of .specialcells that are constant text values. It's purpose is to perform operations on the text within each of these .areas and to store details of this in a (many rows) x (5 columns) array, which is then dumped into a new worksheet - or else into a specified worksheet if there is one that matches the sheet name I have given it. As far as I can tell, wherever possible I have used fully qualified range references.

The problem I have now is that this code works fine on small sheets, or if I run the code for just one larger worksheet, or, if I put in stops and run it in sections - thus slowly loop through all the worksheets in a workbook.
But if I just let the beast have at it, it starts dumping the change details array all over the shop in the other worksheets, erasing values from cells all over the place etc.

I'm assuming this is something to do with execution rate, so how can I make sure an operation (for instance dumping all the change details to a sheet has finished), before it moves on to the next thing (for instance getting the next worksheet)?

I have played with doevents (based on the limited info in the help file) but it doesn't seem to be helping....

Any help would be much appreciated.


Edit:
One thought I had was that I can restructure away from a sub (which writes the change details to the sheet each time it is called for each ws in workbook) and make it a function that returns the array information, which is stored up in a variable in the calling sub and then just dumped once at the end...

Edit 2:
Another thought, at the moment it does a lot of:

For each Area in range.areas
'stuff
next Area

or

For i = 1 to range.areas.count
'stuff on range.areas(i)
next i

Is one of those preferable over the other? (they look about the same to me...)