Lookup Formula Breaks

I have a formula that works fine in the spreadsheet. However when my Visual Basic Application opens the file and saves it as a new file, the formula no longer works. I'm wondering if its because the formula isn't written the best way.
This looks up the max value in Column E (column of numbers that are not sorted), and returns the value in same row in column A. Works great, but after the app runs, it returns the highest value in column A instead.
=LOOKUP(MAX(E2:E82), E2:E82, A2:A82)

Originally Posted by ajvb

I have a formula that works fine in the spreadsheet. However when my Visual Basic Application opens the file and saves it as a new file, the formula no longer works. I'm wondering if its because the formula isn't written the best way.
This looks up the max value in Column E (column of numbers that are not sorted), and returns the value in same row in column A. Works great, but after the app runs, it returns the highest value in column A instead.
=LOOKUP(MAX(E2:E82), E2:E82, A2:A82)

Formula appears ok - did you try Tools, Formula Auditing, Evaluate and step through to see what was happening? (not that Lookup shows much)

Did you press F9 ?
(ie, have you flipped to 'Manual calc'?
(did you suppress ScreenUpdating or EnableEvents?)
---

I haven't slipped into manual as far as I can tell .
Here's what is happening as I look closer:
If I enter numbers in Column E, the formula correctly displays the results that I want.
If I enter any "0's" in Column E, the formula breaks. Sometimes users don't score certain rounds so they get 0 until they score. If I "Clear Contents" the results correctly display again.

Ok, I think I found it. When I check "Transition Formula Evaluation" in the Options, the problem goes away.