Using Excel Find in macro within workbook...code not working

[eja5866]

Hello,
I'm trying to set up a default when Excel opens up so that when the user starts, 'CTRL-F' default option is within 'workbook'. (The default is within 'sheet').

I know that it's some .Find property I can set up as a Workbook_Open function under "ThisWorkbook" module. But I have been unsuccessful here. anyone know the exact code to use?

OR

I can override the default FIND wiht this code I made which searches thru all the sheets. My only problem here is that I cannot stop it from erroring all the time after it finds the text. Here is the code:

*******************************
Sub FindChemical()
On Error GoTo errorHandler
Dim Chemical As String
Dim MyRange As Range
Dim sh As Worksheet


Chemical = InputBox("Enter the Chemical to Search for")
If Chemical = "" Then End

For Each sh In ActiveWorkbook.Worksheets
With sh.Cells

Cells.Find(What:=Chemical, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate


End With
Next sh
MsgBox ("acknowledge")


errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48

End Sub
***********************
Help with solving either of these would be most appreciated. I would prefer the first option, but the second option would be great too!

Thanks!

 

[SkipVought]

Try this...

Sub FindChemical()
    On Error GoTo errorHandler
    Dim Chemical As String
    Dim MyRange As Range
    Dim sh As Worksheet
    Dim rFound As Range
    Dim sFirstAddress As String
    Dim iFirstSheet As Integer, iSheet As Integer
    
    
    Chemical = InputBox("Enter the Chemical to Search for")
    If Chemical = "" Then End
        
    Set rFound = ActiveCell
    iFirstSheet = ActiveSheet.Index
    iSheet = iFirstSheet
    
    Do
        With Sheets(iSheet)
            .Activate
            Set rFound = .Cells.Find(Chemical)
            If Not rFound Is Nothing Then
                sFirstAddress = rFound.Address
    
                Do
                    If Not rFound Is Nothing Then
                        rFound.Select
                        MsgBox ("acknowledge")
                    End If
                    Set rFound = .Cells.FindNext(rFound)
                    If Not rFound Is Nothing Then
                        If rFound.Address = sFirstAddress Then Exit Do
                    End If
                Loop While Not rFound Is Nothing
            End If
        End With
        iSheet = iSheet + 1
        If iSheet > Sheets.Count Then iSheet = 1
    Loop Until iSheet = iFirstSheet
    
    
    Exit Sub
errorHandler:
    MsgBox "There has been an error:  " & Error() & Chr(13) _
        & "Ending Sub.......Please try again", 48

End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[PHV]

Personally, I'd avoid the End instruction ...

 

[eja5866]

Skip,

Thanks!

PHV, - What do you mean by "End instruction"?

 

[SkipVought]

If Chemical = "" Then End


Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

You could instead use something like

If Chemical = "" Then Exit Sub

[tab]or

If Chemical = "" Then GoTo NameHere
'....

NameHere:
End Sub


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

I had to run off, but in order to avoid using GOTO, I would...

  If Trim(Chemical) <> "" then
'do the stuff when you DO have a value in Chemical


  End if
'now the stuff that happens at the END of the procedure


End Sub

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}