Results of a 'find'

[frango_head]

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

[Dave Peterson]

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