Runs fine manually but not fine from wookbook_open

[Warship]

I finally got this macro to run, or so I thought. When I run it manually all is well. However when I run it from workbook_open only the bottom two offsets actually set their values, the top three remain empty.

Sub LoadProtusMateData()

Application.ScreenUpdating = False

'CLEARCONTENTS OF TARGET CELLS
Sheets("MAIN").Select
Range("UserInput").ClearContents 'cols D,E,F
Range("BrokenTimes").ClearContents 'col AV
Range("StartCheck").ClearContents 'col AI

'RETRIEVE DATA
Application.Calculation = xlManual

Sheets("MAIN").Unprotect

For Each num In Range("UnitNum")
With Worksheets("ProtusMateData").Range("ProtusMateDataUnitNum")
Set x = .Find(num.Value, LookIn:=xlValues, Lookat:=xlWhole)
If Not x Is Nothing Then
num.Offset(0, -3).Value = x.Offset(0, 1).Value 'col D
num.Offset(0, -2).Value = x.Offset(0, 2).Value 'col E
num.Offset(0, -1).Value = x.Offset(0, 3).Value 'col F
num.Offset(0, 28).Value = x.Offset(0, 4).Value 'col AI
num.Offset(0, 41).Value = x.Offset(0, 5).Value 'col AV
End If
End With
Next num

Range("UnitCountConstant") = Range("UnitCountProtusData") 'updates to new unit count

Application.Calculation = xlAutomatic

ProtectMain

End Sub

I have tried everything to isolate the problem such as running ONLY this code from workbook_start, same result.

I even tried offsetting to different cols then copy and paste from there to proper cols and it won't copy and paste to cols D-F either.

I tried removing all protection.

I tried moving Range("UnitNum") to col B to avoid negative offsets.

I tried removing all conditional formatting.

There is never an error and again, when ran manually it always works fine.

I'm guessing there is something with cols D, E and F. What else it could be I can't figure.

What's baffling is it runs manually just fine!

I am tried out....Please help!

Thanks in advance for any help and suggestions

 

[galileogali]

I probarye deactivating application.calculation = xlmanual

If you surpass the problem, ¿Which the cause ?

 

[mikerickson]

Have you tried putting this routine in a normal code module and using this Workbook_Open routine. You may need to further qualify some of the ranges.

Private Sub Workbook_Open()
    Call LoadProtusMateData
End Sub

 

[Warship]

mikerickson:
the LoadProtusMateDate is called from the workbook_open event using--
If Range("CorrectUnitCount") = False Then LoadProtusMateData

galileogali:
leaving calculation set to automatic actually worked except it took 9 minutes to complete execution.

So now I question why does it work when executed manually with calculation set to manual?

When ran manually and calculation is manual, it takes a few seconds not 9 minutes.

 

[galileogali]

I probary with

Application.Calculation = xlmanual

but

To including:

application.Range(".....").Precedents.Calculate

Application.Range(".......").calculate.

Before teh sequenses of Offset's

GALILEOGALI

 

[Warship]

galileogali:

Seems that I have it fixed!

Due to you putting me on the right path! Thank You!!!!!

The fix was to replace
Application.Calculation = xlManual
with
Application.Calculation = xlCalculationManual

Not sure what the difference between them are but all preliminary tests were successful.

If it blows up again I'll try your last post

Again, Thanks for the right path! I've been pulling my hair out ALL week.

 

[galileogali]

Obvius, the key was in the calculation

GALILEOGALI

 

[mikerickson]

The difference is that your .Find looks through values. If you are with calculation set to automatic, the .Value of a cell keeps up, with manual, the cell.Value remains the same and the .Find is thrown off.

 

[Warship]

I understand the difference between automatic and manual calculation. That wasn't the issue.

What was changed to fix this was:
replacing Application.Calculation = xlManual
with Application.Calculation = xlCalculationManual
both setting to manually calculate but only one offset correctly.

Using Application.Calculation = xlManual did not offset correctly as in 3 of the offsets were not done yet 2 were done correctly. And this incorrect offset only occured when called from the workbook_open event

Using Application.Calculation = xlCalculationManual did offset correctly.

The values that .Find looks at are references to another wookbook and have no calculation done to them such as:
=[protusdata.xls]Sheet1!$A$4

So my question is this:
Why does Application.Calculation = xlCalculationManual work fine while Application.Calculation = xlManual doesn't?

And further more why was the problem only there when ran from the workbook_open event? When ran from the macro menu and using Application.Calculation = xlManual it did offset correctly.

 

[Gates Is Antichrist]

Something funky is certainly going on if that's really happening. Use debug.print on xlcalculationManual and xlManual to see that they are simply identical. If substituting one for the other really changes behavior, I hate to say it but I'd vote that the culprit is corruption. When you're corrupt, changing a few bytes or simply reordering often causes visibly obvious behavioral changes.

xlManual is simply the name of the constant in earlier (pre-XL97, I believe) Excel versions, retained for backwards compatibility.

FWIW, if you're interested in all aspects of the "calculation process," for me this is the bible - http://www.decisionmodels.com/calcsecretse.htm