Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a spreadsheet
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!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a spreadsheet
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. -- mrice Research Scientist with many years of spreadsheet development experience ------------------------------------------------------------------------ mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931 View this thread: http://www.excelforum.com/showthread...hreadid=564482 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a spreadsheet
Follow this link and download FindLink.xla http://www.oaltd.co.uk/
It is excellent and free! Ryan wrote: Content-Transfer-Encoding: 7bit |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a spreadsheet
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!! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching a spreadsheet
(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 -- brinton92 ------------------------------------------------------------------------ brinton92's Profile: http://www.excelforum.com/member.php...o&userid=36724 View this thread: http://www.excelforum.com/showthread...hreadid=564482 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summarize data from one spreadsheet to other spreadsheet | Excel Worksheet Functions | |||
Working spreadsheet highlighting function for Excel 2007 | Excel Worksheet Functions | |||
extracting data from a spreadsheet by searching on columns | New Users to Excel | |||
Using Excel spreadsheet as input to Access | Excel Discussion (Misc queries) | |||
Spreadsheet merging problems | Excel Worksheet Functions |