Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Results of a 'find'
Hi there Getting a bit rusty with VBA - I'm sure I would have solved this myself a while back! I am testing some workbooks for specific text strings. If the strings exist its an error and the macro should stop and report. If there are no strings thats fine - move to the next test. Can someone give me a pointer as to how to construct the error handling in this instance? I'm looking to wrap something around this: Sheets.Select Cells.Find(What:="not a valid", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate On Error GoTo ErrorTrap1 Any suggestions appreciated... Neil -- frango_head ------------------------------------------------------------------------ frango_head's Profile: http://www.excelforum.com/member.php...o&userid=33355 View this thread: http://www.excelforum.com/showthread...hreadid=531864 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Results of a 'find'
It looks like you want to search through all the worksheets, too.
It that's true, then maybe this will help (I didn't test it!): Option Explicit Sub testme01() Dim FoundCell As Range Dim myText As Variant Dim iCtr As Long Dim Wks As Worksheet Dim foundone As Boolean myText = Array("phrase 1", "Phrase 2", "Phrase 3") For iCtr = LBound(myText) To UBound(myText) foundone = False For Each Wks In ActiveWorkbook.Worksheets Set FoundCell = Wks.Cells.Find(what:=myText, rest_of_parms_here!) If FoundCell Is Nothing Then 'not found on that sheet Else 'found it foundone = True Exit For End If Next Wks If foundone = True Then 'that phrase was found in one of the sheets Else 'not found in any of the sheets End If Next iCtr End Sub Make sure you put all the parms in your .find statement. Excel and VBA will use the last parms that were used--either by the user or by code. It may not give the results you want without those parms. frango_head wrote: Hi there Getting a bit rusty with VBA - I'm sure I would have solved this myself a while back! I am testing some workbooks for specific text strings. If the strings exist its an error and the macro should stop and report. If there are no strings thats fine - move to the next test. Can someone give me a pointer as to how to construct the error handling in this instance? I'm looking to wrap something around this: Sheets.Select Cells.Find(What:="not a valid", After:=ActiveCell, LookIn:=xlValues, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate On Error GoTo ErrorTrap1 Any suggestions appreciated... Neil -- frango_head ------------------------------------------------------------------------ frango_head's Profile: http://www.excelforum.com/member.php...o&userid=33355 View this thread: http://www.excelforum.com/showthread...hreadid=531864 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace results can the appearance of find be different? | Excel Discussion (Misc queries) | |||
fIND GIVES NO RESULTS | Excel Discussion (Misc queries) | |||
find multiple results | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
Find all results dialog box | Excel Discussion (Misc queries) |