Searching a spreadsheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working with a huge workbook that contains many large worksheets. In
these work sheets, there are several dead links to other workbooks, and when
I open this particular book, it prompts me to locate the others. The other
books have either been deleted or can not be located, so I am trying to
search this workbook for the cells that contain the equations with the links
in them so I can delete them. I have tried using Ctrl+F, but was
unsuccessful. If there is an easier way to search multiple sheets within a
book (some are hidden, as are several cells), could some one please let me
know. Thanks Alot!!
 
You might try a macro which scans the sheets sequentially looking fo
formulae. Add a new sheet and call it "New sheet" and try th
following...

Sub Test()
For Each Sheet In Sheets
Formulae = True
On Error GoTo NoFormulae
For Each Cell In Sheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Formulae = True Then
Sheets("New sheet").Cells(65536, 1).End(xlUp).Offset(1, 0
= Sheet.Name
Sheets("New sheet").Cells(65536, 1).End(xlUp).Offset(0, 1
= Cell.Address
Sheets("New sheet").Cells(65536, 1).End(xlUp).Offset(0, 2
= Cell.FormulaR1C1
End If
Next Cell
Next Sheet
Exit Sub
NoFormulae:
Formulae = False
Resume Next
End Sub


This will give the location of all the formulae in the workbook
 
The following macro will check every sheet in the active workbook for
formulas with external references. It will list them all on a new sheet.

Sub FindExtRef()
'This macro should find any formulas with external references, and list them
on a new sheet.
'Declare local variables.
Dim x As Long, c As Range, y As Long, z As Long
Dim NuSht As Worksheet, HitCount As Long, Msg7 As String
On Error Resume Next
Application.Cursor = xlWait
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NuSht = ActiveSheet
HitCount& = 1
'Check every sheet in turn.
DoEvents
For x = 1 To Worksheets.Count
'Activate each sheet in turn.
Sheets(x).Activate
'If this sheet has any formulas, then select all cells with formulas in them.
If HasRx(ActiveSheet) = True Then
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Select
'Check every cell in the selected range.
For Each c In Selection
'Check every character in the formula.
For y = 1 To Len(c.Formula)
'First look for a [ character.
If Mid(c.Formula, y, 1) = "[" Then
'If [ was found, check the rest of the formula for a ! character
For z = y + 1 To Len(c.Formula)
If Mid(c.Formula, z, 1) = "!" Then
'Found an external reference! Store the cell's sheet name, address, and
formula on NuSht.
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" &
ActiveSheet.Name
NuSht.Cells(HitCount&, 2).Value = "'" &
c.Address
NuSht.Cells(HitCount&, 3).Value = "'" &
c.Formula
Exit For
End If
Next z
End If
Next y
Next c
End If
Next x
'Done. Clean up. Add headings for the output rows and resize all columns on
NuSht.
If HitCount& = 1 Then
MsgBox "No external references were found", vbInformation,
"FindExtRef macro"
Application.DisplayAlerts = False
NuSht.Delete
Application.DisplayAlerts = True
GoTo FER_Cleanup
End If
NuSht.Cells(1, 1).Value = "Sheet"
NuSht.Cells(1, 2).Value = "Cell"
NuSht.Cells(1, 3).Value = "Formula"
NuSht.Cells.Select
NuSht.Cells.EntireColumn.AutoFit
Calculate
NuSht.Activate
FER_Cleanup:
'Free object variables.
Set NuSht = Nothing
Set c = Nothing
'Restore the cursor.
Application.Cursor = xlDefault
MsgBox "Done!"
End Sub

When you copy & paste this into a VBA module, watch for any lines that may
have wrapped. They could generate an error in the VB Editor.

Hope this helps,

Hutch
 
(this is "Ryan" the original poster; this is my account name I created)

Thank you all for your help!

I am a novice when it comes to macros, so after a little bit of
experimentation (and failure), I downloaded that .xls file which worked
great!

Thanks agian to all,
Ryan
 
Back
Top