Strange Problem with Formulas

[ricowyder]

Dear users,

I was confronted with a very strange problem. Can anybody help?

Basically, I have 3 worksheets:

No. 1 contains all data
No. 2 contains all filtered data (macro special filter from No. 1)
No. 3 is the output, graphically optimized sheet, which has fixed
formulas to No. 2 (e.g. =Event1!A1)

Imagine, I had only these 3 sheets. Actually, I have many of them, but
there are all paired as No. 1 and 2.
No. 3 is the main sheets, which links too all No. 2's.

To update the file, I have created a macro, which compares one excel
file with the actual one. All worksheets named similarily are
replaced. The macro only looks for No. 1's and replaces all of them.
Since I have other sheets in this file, which are for another purpose,
I had to replace them by looking for the same Worksheet names.

Hence all No. 1 are replaced. No. 2 are not touched at all. Special
filter works still fine afterwards.

However, my cells in No. 3 show all 0 !!!!!

Here are two examples of formulas that both show "0":

=Event6!C2
=IF(ISNA(VLOOKUP("2006-05",Event2!A,4,0)),0,VLOOKUP("2006-05",Event2!
A,4,0))

Fascinatingly, when I click into the cell and press enter, all of a
sudden the true value appears.

I need an expert! Please help!

Here is the macro code of replacing data:

Public Sub ImportAndReplaceMISData()
Dim bStatusBarWasShown As Boolean
bStatusBarWasShown = Application.DisplayStatusBar
Application.DisplayStatusBar = True 'show status bar

' get file path of new data file
Application.StatusBar = "Choose data file..."
Dim vNewDataFilePath As Variant
vNewDataFilePath = Application.GetOpenFilename("Excel Files
(*.xls), *.xls")
If vNewDataFilePath = False Then
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If

'keep reference to KAO workbook
Dim wbKAO As Workbook
Set wbKAO = ActiveWorkbook

'open new data file
Application.StatusBar = "Opening data file..."
Dim wbNewData As Workbook
Set wbNewData = Application.Workbooks.Open(vNewDataFilePath)
If wbNewData Is Nothing Then
Application.StatusBar = "Error opening data file!"
MsgBox "Error opening data file!", vbOKOnly Or vbCritical,
"Data File Error!"
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
Exit Sub
End If

'loop over every sheet of the new data file
' > backup and replace sheets with outdated data
' > add new sheets

On Error GoTo UpdateData_CopyError

Application.StatusBar = "Replacing/copying data..."
Application.DisplayAlerts = False

Dim tNow As Date
tNow = Now
Dim sBackupName As String
sBackupName = "_Backup_" & Format(tNow, "yyyymmdd_hhmmss")

Dim wsNewData As Worksheet
For Each wsNewData In wbNewData.Worksheets

'try to replace old data
Dim bReplaced As Boolean
bReplaced = False

Dim wsOldData As Worksheet
For Each wsOldData In wbKAO.Worksheets
If wsOldData.Name = wsNewData.Name Then
wsOldData.Name = wsOldData.Name & sBackupName 'backup
old data
wsNewData.Copy After:=wsOldData
wsOldData.Delete
bReplaced = True
Exit For
End If
Next

'if no old data to replace, copy it
If Not bReplaced Then
wsNewData.Copy
After:=wbKAO.Worksheets(wbKAO.Worksheets.Count)
End If
Next

'finally, close the new data file and reset Excel
UpdateData_CopyError:

wbNewData.Close

Application.DisplayAlerts = True 'reset alerts
Application.StatusBar = False 'reset status bar
Application.DisplayStatusBar = bStatusBarWasShown
End Sub

 

[Dave Peterson]

Try adding one of these:

Application.Calculate
Application.CalculateFull
Application.CalculateFullRebuild

after you do the replace. Depending on your version of excel, you may not have
all of these.

If that doesn't work, you could always add code to change all the equal signs to
equal signs in that worksheet.

It should make excel see a change to each formula and re-evaluate each one.