+ Reply to Thread
Results 1 to 3 of 3

type mismatch here!

  1. #1
    J_J
    Guest

    type mismatch here!

    Hi,
    the below code (which was discussed here some time ago) finds and highlights
    all occurances of watched string on active sheet. How can I put a loop to it
    so that it will do the same one after the other one for all worksheets in
    the workbook, and highlights all of them?.
    TIA
    J_J



    Option Explicit
    Sub find_all()

    Dim myRng As Range
    Dim FoundCell As Range
    Dim AllCells As Range
    Dim FirstAddress As String
    Dim whatToFind As String
    Dim sh As Worksheet

    whatToFind = "abc"

    Set myRng = ActiveSheet.Range("A1:IV3000")

    With myRng
    Set FoundCell = .Cells.Find(what:=whatToFind, _
    after:=.Cells(.Cells.Count), LookIn:=xlValues, _
    lookat:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False)

    If FoundCell Is Nothing Then
    'do nothing
    Else
    FirstAddress = FoundCell.Address
    Do
    If AllCells Is Nothing Then
    Set AllCells = FoundCell
    Else
    Set AllCells = Union(FoundCell, AllCells)
    End If
    Set FoundCell = .FindNext(FoundCell)
    Loop While Not FoundCell Is Nothing _
    And FoundCell.Address <> FirstAddress
    End If
    End With

    If AllCells Is Nothing Then
    MsgBox whatToFind & " wasn't found"
    Else
    AllCells.Select
    End If
    End Sub




  2. #2
    Dave Peterson
    Guest

    Re: type mismatch here!

    Wrapping it in a loop worked ok for me:

    Option Explicit
    Sub find_all()

    Dim myRng As Range
    Dim FoundCell As Range
    Dim AllCells As Range
    Dim FirstAddress As String
    Dim whatToFind As String
    Dim wks As Worksheet

    whatToFind = "abc"

    For Each wks In ActiveWorkbook.Worksheets
    wks.Select
    Set AllCells = Nothing

    Set myRng = wks.Range("A1:IV3000")

    With myRng
    Set FoundCell = .Cells.Find(what:=whatToFind, _
    after:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    lookat:=xlPart, searchorder:=xlByRows, _
    searchdirection:=xlNext, MatchCase:=False)

    If FoundCell Is Nothing Then
    'do nothing
    Else
    FirstAddress = FoundCell.Address
    Do
    If AllCells Is Nothing Then
    Set AllCells = FoundCell
    Else
    Set AllCells = Union(FoundCell, AllCells)
    End If
    Set FoundCell = .FindNext(FoundCell)
    Loop While Not FoundCell Is Nothing _
    And FoundCell.Address <> FirstAddress
    End If
    End With

    If AllCells Is Nothing Then
    MsgBox whatToFind & " wasn't found on worksheet: " & wks.Name
    Else
    AllCells.Select
    End If
    Next wks
    End Sub





    J_J wrote:
    >
    > Hi,
    > the below code (which was discussed here some time ago) finds and highlights
    > all occurances of watched string on active sheet. How can I put a loop to it
    > so that it will do the same one after the other one for all worksheets in
    > the workbook, and highlights all of them?.
    > TIA
    > J_J
    >
    > Option Explicit
    > Sub find_all()
    >
    > Dim myRng As Range
    > Dim FoundCell As Range
    > Dim AllCells As Range
    > Dim FirstAddress As String
    > Dim whatToFind As String
    > Dim sh As Worksheet
    >
    > whatToFind = "abc"
    >
    > Set myRng = ActiveSheet.Range("A1:IV3000")
    >
    > With myRng
    > Set FoundCell = .Cells.Find(what:=whatToFind, _
    > after:=.Cells(.Cells.Count), LookIn:=xlValues, _
    > lookat:=xlPart, searchorder:=xlByRows, _
    > searchdirection:=xlNext, MatchCase:=False)
    >
    > If FoundCell Is Nothing Then
    > 'do nothing
    > Else
    > FirstAddress = FoundCell.Address
    > Do
    > If AllCells Is Nothing Then
    > Set AllCells = FoundCell
    > Else
    > Set AllCells = Union(FoundCell, AllCells)
    > End If
    > Set FoundCell = .FindNext(FoundCell)
    > Loop While Not FoundCell Is Nothing _
    > And FoundCell.Address <> FirstAddress
    > End If
    > End With
    >
    > If AllCells Is Nothing Then
    > MsgBox whatToFind & " wasn't found"
    > Else
    > AllCells.Select
    > End If
    > End Sub


    --

    Dave Peterson

  3. #3
    J_J
    Guest

    Re: type mismatch here!

    Worked perfectly well here too...
    Thanks Dave.
    J_J

    "Dave Peterson" <[email protected]> wrote in message
    news:[email protected]...
    > Wrapping it in a loop worked ok for me:
    >
    > Option Explicit
    > Sub find_all()
    >
    > Dim myRng As Range
    > Dim FoundCell As Range
    > Dim AllCells As Range
    > Dim FirstAddress As String
    > Dim whatToFind As String
    > Dim wks As Worksheet
    >
    > whatToFind = "abc"
    >
    > For Each wks In ActiveWorkbook.Worksheets
    > wks.Select
    > Set AllCells = Nothing
    >
    > Set myRng = wks.Range("A1:IV3000")
    >
    > With myRng
    > Set FoundCell = .Cells.Find(what:=whatToFind, _
    > after:=.Cells(.Cells.Count), _
    > LookIn:=xlValues, _
    > lookat:=xlPart, searchorder:=xlByRows, _
    > searchdirection:=xlNext, MatchCase:=False)
    >
    > If FoundCell Is Nothing Then
    > 'do nothing
    > Else
    > FirstAddress = FoundCell.Address
    > Do
    > If AllCells Is Nothing Then
    > Set AllCells = FoundCell
    > Else
    > Set AllCells = Union(FoundCell, AllCells)
    > End If
    > Set FoundCell = .FindNext(FoundCell)
    > Loop While Not FoundCell Is Nothing _
    > And FoundCell.Address <> FirstAddress
    > End If
    > End With
    >
    > If AllCells Is Nothing Then
    > MsgBox whatToFind & " wasn't found on worksheet: " & wks.Name
    > Else
    > AllCells.Select
    > End If
    > Next wks
    > End Sub
    >
    >
    >
    >
    >
    > J_J wrote:
    >>
    >> Hi,
    >> the below code (which was discussed here some time ago) finds and
    >> highlights
    >> all occurances of watched string on active sheet. How can I put a loop to
    >> it
    >> so that it will do the same one after the other one for all worksheets in
    >> the workbook, and highlights all of them?.
    >> TIA
    >> J_J
    >>
    >> Option Explicit
    >> Sub find_all()
    >>
    >> Dim myRng As Range
    >> Dim FoundCell As Range
    >> Dim AllCells As Range
    >> Dim FirstAddress As String
    >> Dim whatToFind As String
    >> Dim sh As Worksheet
    >>
    >> whatToFind = "abc"
    >>
    >> Set myRng = ActiveSheet.Range("A1:IV3000")
    >>
    >> With myRng
    >> Set FoundCell = .Cells.Find(what:=whatToFind, _
    >> after:=.Cells(.Cells.Count),
    >> LookIn:=xlValues, _
    >> lookat:=xlPart, searchorder:=xlByRows, _
    >> searchdirection:=xlNext, MatchCase:=False)
    >>
    >> If FoundCell Is Nothing Then
    >> 'do nothing
    >> Else
    >> FirstAddress = FoundCell.Address
    >> Do
    >> If AllCells Is Nothing Then
    >> Set AllCells = FoundCell
    >> Else
    >> Set AllCells = Union(FoundCell, AllCells)
    >> End If
    >> Set FoundCell = .FindNext(FoundCell)
    >> Loop While Not FoundCell Is Nothing _
    >> And FoundCell.Address <> FirstAddress
    >> End If
    >> End With
    >>
    >> If AllCells Is Nothing Then
    >> MsgBox whatToFind & " wasn't found"
    >> Else
    >> AllCells.Select
    >> End If
    >> End Sub

    >
    > --
    >
    > Dave Peterson




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1