Updating Issue

Hi...

I have a fairly complex spreadsheet. Amazingly, formulas don't work unless I
enter the cell and press return and then the value shows. I can't do this for
every cell.

Why is this happening? and is there any code I could run in my auto_open to
prevent it happening? Auto calculation is fully enabled.

Thanks in advance

Hi,

Sometimes Excel can incorrectly believe that the cells (or precedents) are (or you want them to be) formatted as text...

*Try find & replace all on the "=" sign ie put the equals sign in both the find & the replace fields. This may cause Excel to "wake up".

*Check the cell formatting using [F5] - Special & seeing what is selected when you choose various options.

*Try changing the cell formatting...

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

Hi...

No change. Is there not some code that could poke Excel when I open the file?

Thanks

Gordon...

"broro183" wrote:

>
> Hi,
>
> Sometimes Excel can incorrectly believe that the cells (or precedents)
> are (or you want them to be) formatted as text...
>
> *Try find & replace all on the "=" sign ie put the equals sign in both
> the find & the replace fields. This may cause Excel to "wake up".
>
> *Check the cell formatting using [F5] - Special & seeing what is
> selected when you choose various options.
>
> *Try changing the cell formatting...
>
> hth
> Rob Brockett
> NZ
> Always learning & the best way to learn is to experience...
>
>
> --
> broro183
> ------------------------------------------------------------------------
> broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
> View this thread: http://www.excelforum.com/showthread...hreadid=568250
>
>

Hi Gordon,

Sorry for not getting back to you sooner - I've been away from home.

I'm just learning too but I had a play & came up with the following (hard to test b/c I don't have your problem at the moment), try pasting the following code into your "thisworkbook" module:

Option Explicit
Private Sub Workbook_Open()
'KickStartCalcs
Application.ScreenUpdating = False
'found this when playing - it may help...
If Application.CalculationState = xlDone Then
MsgBox "Calculation is uptodate when file opened."
Else
MsgBox "Needs to calculate - being done now..."
Application.CalculateFull
End If

Dim ws As Worksheet
Dim OriginalSheet As Worksheet
Dim OriginalCell As Range
Dim EqtnRange As Range
Dim cell As Range
'To id original location for return after macro has run.
Set OriginalSheet = ActiveSheet
Set OriginalCell = ActiveCell

For Each ws In ThisWorkbook.Worksheets
With ws
.Select
'23 was the value when I recorded a macro using "go to" all _
types of formulae ([F5], Special, Formulas - all ticked)
Set EqtnRange = .Cells.SpecialCells(xlCellTypeFormulas, 23)
For Each cell In EqtnRange
'The following should save the need for clicking _
into all the cells
cell.Activate
Next cell
End With
Next ws

'return to starting point & free memory
OriginalSheet.Select
OriginalCell.Select
Set EqtnRange = Nothing
Set OriginalSheet = Nothing
Set OriginalCell = Nothing

MsgBox "Every cell with a formula in this workbook has been activated."
Application.ScreenUpdating = True
End Sub


hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...