Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching an Entire Workbook
Public Sub HighlightData()
Dim myDatum As Range, myRange As Range Set myRange = Workbooks("List.xls").Worksheets("List").Range("Li st") For Each myDatum In myRange Windows("AllData.xls").Activate Cells.Find(What:=myDatum, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate On Error GoTo ErrorHandler Selection.Font.Bold = True With Selection.Interior ..ColorIndex = 6 'Yellow ..Pattern = xlSolid End With Next ErrorHandler: ActiveSheet.Next.Select Resume End Sub Can someone help me refine this code? It works quite well, but I would like to replace the Error Handling with code that says what is actually supposed to be done - that is, to select the next sheet when the item being looked for is not on the current sheet since this is something that is expected and not actually an error. Also, there is the small problem (error) of it trying to select the next sheet after the last sheet. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching an Entire Workbook
Apologies if you already knbew about it, but are you using a version of Excel
that doesn't allow you to select 'entire workbook' as the search area? -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "SlimSlender" wrote in message ps.com... Public Sub HighlightData() Dim myDatum As Range, myRange As Range Set myRange = Workbooks("List.xls").Worksheets("List").Range("Li st") For Each myDatum In myRange Windows("AllData.xls").Activate Cells.Find(What:=myDatum, After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _ MatchCase:=False).Activate On Error GoTo ErrorHandler Selection.Font.Bold = True With Selection.Interior .ColorIndex = 6 'Yellow .Pattern = xlSolid End With Next ErrorHandler: ActiveSheet.Next.Select Resume End Sub Can someone help me refine this code? It works quite well, but I would like to replace the Error Handling with code that says what is actually supposed to be done - that is, to select the next sheet when the item being looked for is not on the current sheet since this is something that is expected and not actually an error. Also, there is the small problem (error) of it trying to select the next sheet after the last sheet. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.809 / Virus Database: 551 - Release Date: 09/12/2004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching multiple fields and then print the entire row from resul | Excel Worksheet Functions | |||
VLookup Not Searching the entire cell for Match | Excel Discussion (Misc queries) | |||
Relpace entire address with street name only, searching with wildc | Excel Worksheet Functions | |||
searching all workbook sheets | Excel Worksheet Functions | |||
Identifying an item in one col/row and searching an entire spreads | Excel Worksheet Functions |