Range Reclaculate problems

[RincewindWIZZ]

I'm having a problem with a VBA subroutine in Excel 2003 SP3 running on a Toshiba Tecra M4 XP SP3, 1GB 2*750MHz)
The basic spreadsheet structure is a Main page of 100 rows and 200 cols using data from 8 other pages mostly by lookup, offset, match and index. The first 30 rows are user entered data, the remainder are all the calculations. Each row in the main page is independent of every other row (they are multiple copies of the same formulae)
There are quite a lot of validation, data lists and conditional formatting in the input cells

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) Range.Calculate.
I have tried Range(“rng”).Calculate and Range(rng).calculate (where rng is declared as a range) and both produce 1004 errors so I have resorted to the more cumbersome Range(Cells(a,b),cells(c,d)) 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 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 minutes without ending.

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 after a Range.Calculate especially when all the cells in the range have no dpendents outside the range

Thanks for any suggestions

Sub MinCost()

ColOQ = 24      'Average Order
ColMult = 33    'Shipping Outer contains
ColMin = 25     'Min OQ
ColCost = 55    'Fully Loaded Cost
ColMax = 21     'Seasons sales
MaxIter = 5     ' Maximum Iterations
MinRow = 9      'First row that data in which data can be entered
MaxRow = 109    'Last row in which data can be entered
MinCOl = 3      'Min col for recalculate
MaxCol = 200    'maxCol for recalculate
' Parameters are Columns for Order Size, Multiple, Cell to minimise
    Dim rng As Range
    Dim lRowSelected As Long
'    Application.Calculation = xlCalculationManual
    For Each rng In Selection.Rows
        lRowSelected = rng.Row
        If lRowSelected >= MinRow And lRowSelected <= MaxRow Then
           Multoq = Cells(lRowSelected, ColMult)
           Minoq = Cells(lRowSelected, ColMin)
           Maxoq = Cells(lRowSelected, ColMax)
           Incoq = Max(Multoq, Int((Maxoq - Minoq) / MaxIter))
           Incoq = Multoq * Int(Incoq / Multoq + 0.99999)
           BestCost = 10 ^ 9
           BestOQ = Minoq
           OQ = Max(Incoq, Minoq)  'initial Order Quantity
           Do While OQ < Maxoq      'loop though possible values
              Cells(lRowSelected, ColOQ).Value = OQ
' Range("rng").Calculate 'recalculate values in this row
'Range(rng).Calculate
' Range(Cells(lRowSelected, MinCOl), Cells(lRowSelected, MaxCol)).Calculate
 Calculate
              If Cells(lRowSelected, ColCost) < BestCost Then   'Is this best so far?
                 BestCost = Cells(lRowSelected, ColCost)
                 BestOQ = OQ
              End If
              OQ = OQ + Incoq                       ' increment and try again
              Cells(5, 3).Value = OQ                'put current answers in cell to see progress
              Cells(4, 3).Value = BestCost
           Loop
        Else
           MsgBox "Please select a valid row before calculating the best OQ"
        End If
        Cells(lRowSelected, ColOQ) = BestOQ 'store answer
    Next rng
   Application.Calculation = xlCalculationAutomatic
'   Calculate
End Sub

 

[sweater_vests_rock]

1) Range.Calculate.
I have tried Range(“rng”).Calculate and Range(rng).calculate (where rng is declared as a range) and both produce 1004 errors so I have resorted to the more cumbersome Range(Cells(a,b),cells(c,d)) which works OK. Why does Range(“rng”).Calculate produce 1004 error?

i think rng.Calculate should work for you.

i don't know about the lock-up issue...

ben.

 

[xenou]

Why do you need to calculate each row one at a time? I.e., why not set calculation to manual, process rows, and set calculation back to automatic?

Calculation issues are a bit mysterious.

AB

 

[RincewindWIZZ]

Well the objective is to process the calculations lots of times for each row.
Specifically,
1.Change the value of ColOQ;
2.Get excel to recalculate the value of ColCost (which is a sum of lots of other values in the row which in turn depend on data on other sheets) on the basis of the new ColOQ value;
3. See if the 'answer' in ColCost is the best so far and if so store it and the associated ColOQ
4. Repeat for the next value (ColOQ+IncOQ)
Then, having completed the calculations for each of the possible ColOQ values (incremented by IncOQ) restore the best result to ColOQ.

Seems simple enough but its just very slow
for example, I dont understand why, when I use Calculate in the loop it takes 6 or 7 seconds but when I use Row.Calculate it is much quicker (< 0.5 seconds) There are no values outside the row that depend on the values in the row so, as I understand it excel shuld do the same set of sums in both cases
Confused

 

[xenou]

It's a puzzler alright. If you can write any values into memory and use them in code (as variables) rather than reading them from your sheets, that would help. That's about all I can suggest.