PDA

View Full Version : Formula Error Message in Excel 2007 Sheet



Cyberdude
02-25-2010, 10:27 AM
I'm trying to convert an Excel 2003 workbook to an Excel 2007 workbook, and I keep getting the following error message:
"A formula in this worksheet contains one or more invalid references.
Verify that your formulas contain a valid path, workbook, range name, and cell references"

There is no indication where these invalid references might be, and this worksheet has LOTS of formulas. I clicked the error detection button, but it doesn't do anything. Does anyone have a technique for finding the invalid formulas? :igiveup:

SamT
02-25-2010, 02:12 PM
Sub Show_All_Sheets_In_Workbook()
Dim i As Long
ActiveWorkbook.Unprotect
For i = 1 To Sheets.Count
Sheets(i).Unprotect
Sheets(i).Visible = True
Next
End Sub

Sub Color_Cells_With_Formulas()
'This will color all Cells containing a fomula in
' all sheets in the Active Workbook
Dim All As Range
Dim n As Long
For n = 1 To Sheets.Count
Sheets(n).Activate
Sheets(n).Range("A1:IV65536").Select
Selection.SpecialCells(xlFormulas).Interior.ColorIndex = 3
Next
End Sub

SamT
02-25-2010, 02:15 PM
Sub Color_Cells_With_Names()
'This will color all Cells containing a fomula in
' all sheets in the Active Workbook
Dim n As Long
For n = 1 To ActiveWorkbook.Names.Count
Range(ActiveWorkbook.Names(n)).Interior.ColorIndex = 5
Next
End Sub

SamT
02-25-2010, 02:17 PM
Sub AllNummericCells()
'This will color all cells with
Dim rFcells As Range
Dim rAcells As Range
Dim n As Long
For n = 1 To Sheets.Count
Sheets(n).Activate
'Set variable to all used cells
Set rAcells = ActiveSheet.UsedRange
On Error Resume Next 'In case of no numeric formula or constants.
'Set variable to all numeric formulas
Set rFcells = rAcells.SpecialCells(xlCellTypeFormulas)
'Determine which type of numeric data (formulas, constants or none)
If rFcells Is Nothing Then
MsgBox "You Worksheet contains no Formulas"
End
Else
Set rAcells = rFcells
End If
On Error GoTo 0
rAcells.Interior.ColorIndex = 8
Next
End Sub

Aussiebear
02-25-2010, 02:52 PM
[vba]
ActiveWorkbook.Unprotect
]/vba]

Hmmmm...... Can you have this line without Excel asking for your password, and if so, have you left the workbook now with not protection?

SamT
02-25-2010, 04:05 PM
IT worked for me. I understand that in 2007, there is an issue with it.

Yes, the Workbook is unprotected. Thhose Subs are utilities I wrote to help me troubleshoot other peoples workbooks.

My own Workbooks never have problems:whistle: :rolleyes: :yeahright :rotflmao: :mbounce:

Cyberdude
02-25-2010, 08:23 PM
Hey, nice work, Sam! I'll get to work on it tomorrow morning. This is the weirdist proplem I've had today, except for trying to assign a macro to a shape (rectangle). I still haven't licked that one. All my old shapes accept assignment OK. (Pffft!)
Sid :snooze