Searching a spreadsheet

[Ryan]

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!!

[mrice]

You might try a macro which scans the sheets sequentially looking for formulae. Add a new sheet and call it "New sheet" and try the 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.

[donoteventry;removes;[email protected]]

Follow this link and download FindLink.xla http://www.oaltd.co.uk/

It is excellent and free!



Ryan <[email protected]> wrote:

>
>Content-Transfer-Encoding: 7bit

[Tom Hutchins]

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

"Ryan" wrote:

> 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!!

[brinton92]

(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