REALLY WEIRD problem

Zaphodski

Board Regular
Joined
Jun 12, 2002
Messages
85
I don't know how to look this up it's so weird - at least for me:

I have a 19 Mb .xls file which works fine. Lots of financial calcs for 3 years into the future. I copied it and reduced it to 1 year to lighten the load on LAN & systems(for sharing).

It contains a macro that ends with Application.CalculateFull and runs on open. It works and has always worked without problem. In the daughter (smaller) file, which now has some different data content (but still structurally identical) any second attempt to recalculate results in uniform error return "#VALUE!" in each cell.

This happens when I add new data, for example. What gets me though is if I then save it whilst it displays the "#VALUE!" message, close it and re-open it I get the correct values that I always used to get.

I have identified the function this appears related to, but can't figure out what is going on. Any ideas please?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Actuariojf

New Member
Joined
Aug 29, 2002
Messages
15
I am not an expert in Excel, howevcer sometimes this kind of problems happen when you have Circular References. Check if you have one.

I hope it helps!

Thanks!
 

Zaphodski

Board Regular
Joined
Jun 12, 2002
Messages
85
Thanks,

But that's not it :eek:(

I think it is more likely some system (memory?) conflict with something deep inside excel - since rebooting the problem is less frequent, but still occurs. There is NOTHING fundamentally wrong with the data or the structure - which is why the 'correct' answers come out in the end. It is temperamental; as if Excel has a fuzzy dision process!
 

applebyd

Active Member
Joined
May 27, 2002
Messages
348
What are you getting if you run a trace error
from the auditing tool bar.

That at least will show you where the Error comes from.

HTH
DaveA
 

Zaphodski

Board Regular
Joined
Jun 12, 2002
Messages
85
Thanks, it settles on a function I wrote - :eek:) obvious cause I'd think - but the same function has worked for about a year and I haven't altered the code or any of the values it loads since. The same function also works fine (as does the whole workbook) when restarted after Excel is closed down. I have copied the code to a .txt file and re-inserted it which does seem to have improved things a bit. I am increasingly suspicious this is more a local machine problem than an Excel issue, something is conflicting in memory (TSR?). Thanks for all the advice.
 

Forum statistics

Threads
1,143,923
Messages
5,721,557
Members
422,370
Latest member
A Nonomus

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
Top