Ahhh. I've suffered/enjoyed the same symptom.



Vasant Nanavati wrote:

That may not have been what I said, but it was probably what I meant! <g

--

Vasant

"Dave Peterson" wrote in message
...
You sure? I've never seen that.

But I have seen this:

http://support.microsoft.com/?scid=243495
XL: Calculate Message Remains in Status Bar If 65,536 Formula References

From that KB:

Microsoft Excel will correctly calculate all formulas. However, the
"Calculate"
message in the status bar indicates that Microsoft Excel can no longer
track the
formula dependencies and is calculating every formula in the workbook
after each
change. If you are unsure when the last calculation event took place and
you
want to be sure your formulas are up to date, simply start a calculation
manually. To do this, press CTRL+ALT+F9 and wait until "Calculating Cells:
n%"
disappears and "Calculate" returns to the status bar.

Vasant Nanavati wrote:

If you have a large workbook with many formulas, Excel does sometimes
seem
to unilaterally reset the calculation mode to manual.

--

Vasant

"Hari" wrote in message
...
Hi,

For the last 4 or 5 days I was running "all over the place" literally
as I
had a code which was executing as expected in my Colleagues workplace,
but
at my computer and home computer it wasnt. Please refer to "Automatic
opening of files seeming to have cache" posted 3 or 4 days back.

I think I might have finally found the reason to this. ( Please also
refer
Microsoft.public.excel.misc -- "Excel Manual calculation is stubborn"
posted today.

When I use to run the code (pls see below) then even if my calculation
was
set to automatic before the macro was run, excel would change it to
manual
during the course of its running ( I checked the calculation setting
at
when
the macro had executed) and probably it wasnt reading all the files as
expected.

Please tell me why this macro is behaving the way it is and also how
to
rectify the same. Interestingly the same macro when run in my
colleagues
computer will not tamper with the calculation settings(!!)


Regards,
Hari
India

Sub OpenWorkbooksInLocation()

Application.ScreenUpdating = True
Dim i As Integer
Dim p As String

Workbooks.Open "C:\Documents and
Settings\hprasadh\Desktop\Janice\Project comparison\IEX Format.xls"
Windows("IEX Format.xls").Activate
Range("A3:F7000").Select
Selection.Clear
Application.Goto Reference:="R1C1"
With Application.FileSearch
.NewSearch
.LookIn = "C:\CCAPPS\ttlview\TMP\" & Format(Now, "yyyy-mm-dd")
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.Execute
For i = 1 To .FoundFiles.Count

Set wb = Workbooks.Open(filename:=.FoundFiles(i))
p = .FoundFiles(i)
Call TransferIEXExceldata(p)
Next i
End With
Application.ScreenUpdating = True
Windows("IEX format").Activate
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs filename:= _
"C:\Documents and Settings\hprasadh\Desktop\Janice\Project
comparison\IEX format " & " " & CStr(Format(Now, "yyyy-mm-dd")),
FileFormat:=xlNormal
Application.DisplayAlerts = True







End Sub


Public Function TransferIEXExceldata(ByVal p As String)
'
' TransferIEXExceldata Macro
' Macro recorded 6/9/2004 by Hari Prasadh
'

'
Dim q As String
Windows("IEX Format.xls").Activate
Application.Goto Reference:="R1C1"

p = Application.WorksheetFunction.Substitute(p,
"c:\ccapps\ttlview\tmp\" & Format(Now, "yyyy-mm-dd") & "\", "")

Windows(p).Activate
Application.Goto Reference:="R1C1"
Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A3"),
DataType:=xlDelimited,
_
TextQualifier:=xlNone, ConsecutiveDelimiter:=True, Tab:=False,
Semicolon _
:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo:=Array( _
Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1)),
TrailingMinusNumbers _
:=True
Range("D3").Select
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 5).Range("A1").Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Application.Goto Reference:="R13C1"
Range("A13:E13").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(0, 5).Range("A1").Select
Windows(p).Activate
Application.Goto Reference:="R3C4"
Application.CutCopyMode = False
Selection.Copy
Windows("IEX format.xls").Activate
ActiveSheet.Paste
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste
Application.Goto Reference:="R1C1"
Windows(p).Activate
Rows("3:3").Select
Selection.Clear
Application.Goto Reference:="R1C1"
Workbooks(p).Close SaveChanges:=False
Windows("IEX format.xls").Activate

End Function





--

Dave Peterson


--

Dave Peterson