Results 1 to 3 of 3
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Find (VBA/Excel-Word/2000)

    Frequently when writing code for Word I use the command:
    <pre> If Not Selection.Find.Found Then</pre>


    I have been unable to find an equivalent command for use on the Excel environment. Consequently, I use a construct along the following lines:
    <pre> Range("A:A").Select
    Range("A6").Activate
    On Error Resume Next
    Selection.Find(What:="{Undefined}", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=True).Activate
    On Error GoTo 0
    While ActiveCell.Address <> "$A$6"
    '
    '
    ' A whole bunch of code
    '
    '
    Range("A:A").Select
    Range("A6").Activate
    On Error Resume Next
    Selection.Find(What:="{Undefined}", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=True).Activate
    On Error GoTo 0

    Wend
    Exit Sub
    </pre>


    I would feel more comfortable, if it was not necessary to use the "On Error Resume Next" command. Is anyone aware of a more elegant approach for code in Excel?

    TIA
    Regards
    Don

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Find (VBA/Excel-Word/2000)

    Stumbling around in the Excel object model, it appears you can do something like this:

    <pre>Dim rng As Excel.Range, rngFound As Excel.Range, strFirst As String
    Set rng = ActiveSheet.Range("A:A")
    With rng
    .Select 'for debugging
    .Cells(, 1).Activate 'make first cell active
    Set rngFound = .Find(What:="{Undefined}", SearchOrder:=xlByColumns, _
    MatchCase:=True)
    If Not (rngFound Is Nothing) Then
    strFirst = rngFound.Address 'otherwise the search may wrap infinitely
    Do
    MsgBox rngFound.Address 'Your actual code goes here...
    Set rngFound = .FindNext(Range(rngFound.Address))
    Loop While Not (rngFound Is Nothing) And (rngFound.Address <> strFirst)
    End If
    End With
    If Not (rngFound Is Nothing) Then Set rngFound = Nothing
    If Not (rng Is Nothing) Then Set rng = Nothing</pre>

    Does that make sense?

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Find (VBA/Excel-Word/2000)

    Thank you Jefferson.
    The only point on which I can take issue is your use of the word "Stumbling".
    Regards
    Don

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •