Runs fine manually but not fine from wookbook_open

Warship

Warship

Well-known Member
Joined
Jul 17, 2007
Messages
1,055
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
I probarye deactivating application.calculation = xlmanual

If you surpass the problem, ¿Which the cause ?
 
Upvote 0
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.
Code: 
Private Sub Workbook_Open()
    Call LoadProtusMateData
End Sub
 
Upvote 0
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.
 
Upvote 0
I probary with

Application.Calculation = xlmanual

but

To including:

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

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

Before teh sequenses of Offset's

GALILEOGALI
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA if a row is all blank in a range of columns, highlight the blanks and the headers
2 3 4
Replies
31
Views
1K
AnnAnn
A
B
  • Question Question
VBA Code to Send Mail to skip blank cells
Replies
1
Views
364
Micron
M
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
468
NdNoviceHlp
NdNoviceHlp
M
  • Question Question
[VBA]Copy data from specify cell and input value to another sheet with time stamp
Replies
2
Views
459
maiwarits
M
I
  • Solved
Write existing short code another way to rule out column shift
Replies
6
Views
272
ipbr21054
I

Forum statistics

Threads
1,225,481
Messages
6,185,239
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top