VBA Range Recalculate Locks spreadsheet

[Rincewind]

Excel 2003 is driving me nuts
(Toshiba M4 XP SP3, 1GB 2*750MHz)
I have a medium size spreadsheet. The basic structure is
A Main page of 100 rows and 200 cols using data from 8 other pages
Each row in the main page is independent (Multiple copies of the same row)

I have a VBA subroutine that tries to find the value for a particular cell that gives the smallest value in another cell by iterating through a number of possible values (See below)

I have a couple of ‘challenges’
1) I have tried Range(“rng”).Calculate and Range(rng).calculate and both produce 1004 errors so I have resorted to the more cumbersome Range(Cells( etc . . . . which works OK. Why does Range(“rng”).Calculate produce 1004 error?
2) Recalculation Issues
i) If I do a full calculate each time through the loop its unacceptably slow (1.5 secs / time through the OQ<MaxOQ loop)
ii) If I switch off set manual recalculation before the loop its much quicker (approx .5 secs/time through the OQ<MaxOQ loop)
iii) If I change from Calculate to Range.calculate it’s a decent speed (<.05 sec/loop) but then excel locks up on the final statement that sets recalculation back to automatic (and does a recalculate). Locks up means drives the processor flat out for more than 5 mintes
So what is causing this Excel lock up?
If I have not previously done a Range.calculate, Calculate works fine so why does it get stuck?

Thanks for any suggestions

The Routine (Lines in capitals are where I have summarised sections of the code.

Please Login or Register  to view this content.

[royUK]

Welcome to the Forum. Please read the Forum Rules & use Code Tags in future. It would help if we could see how you have declared your variables.

[RincewindWIZZ]

Whoops - sorry. Thought it looked a bit messy. Didn't know about code tag.
Full code follows.
Thoughts appreciated.

Please Login or Register  to view this content.

[royUK]

I think that you are looking for

Please Login or Register  to view this content.

[RincewindWIZZ]

Thanks - that works just fine. However, I don't really understand why Range(rng).Calculate does not work?

However the real problem is No 2 in the original post. Why does excel "lock up" after the loop when recalculation is set back to automatic?
Any suggestions on this one?
TFAI