Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Get 'find all' results in worksheet (xl 2002 sp2)

    I would like to get in a worksheet the list displayed when I run the command Edit >> Find >> ****** >> Find all

    "****** "stands for the (variable) string used in one run (entered in the field "Find what")

    Result expected:
    <UL> <LI>In the first column, I would get the reference of the cell concerned
    <LI>In the second column I would get the full content of the cell[/list]Thanks for your help

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get 'find all' results in worksheet (xl 2002 sp2)

    Does this macro do what you want?

    Steve
    <pre>option explicit
    Sub ListFinds()
    Dim rCell As Range
    Dim lRow As Long
    Dim wksNew As Worksheet
    Dim wksSearch As Worksheet
    Dim sSearch As String
    Dim sValue As String
    Dim iIgnoreCase As Integer
    Dim istart As Integer
    Dim iLen As Integer

    Set wksSearch = ActiveSheet
    Set wksNew = Worksheets.Add

    sSearch = InputBox("Search for What?")
    If sSearch = "" Then Exit Sub

    iLen = Len(sSearch)
    iIgnoreCase = MsgBox(prompt:="Do you want to Ignore case?", _
    Buttons:=vbYesNo)

    wksSearch.Activate

    wksNew.Cells(1, 1) = "Searching for:"
    wksNew.Cells(1, 2) = sSearch
    wksNew.Cells(1, 2).Font.ColorIndex = 3
    If iIgnoreCase = vbYes Then
    wksNew.Cells(2, 1) = "Ignore Case"
    sSearch = UCase(sSearch)
    Else
    wksNew.Cells(2, 1) = "Case Sensitive"
    End If
    wksNew.Cells(3, 1) = "Address"
    wksNew.Cells(3, 2) = "Value"

    lRow = 4

    For Each rCell In wksSearch.UsedRange
    sValue = rCell.Value
    If iIgnoreCase = vbYes Then sValue = UCase(sValue)

    istart = InStr(sValue, sSearch)
    If istart > 0 Then

    wksNew.Cells(lRow, 1) = rCell.Address(False, False)
    wksNew.Cells(lRow, 2) = rCell.Value

    wksNew.Cells(lRow, 2).Characters(Start:=istart, _
    Length:=iLen).Font.ColorIndex = 3

    lRow = lRow + 1
    End If
    Next rCell
    wksNew.Activate
    Range("A:B").Columns.AutoFit
    Range("A4").Select
    ActiveWindow.FreezePanes = True
    End Sub
    </pre>


  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get 'find all' results in worksheet (xl 2002 sp2)

    The Excel Find command will not do what you are asking. You could write a macro to do this. If you want help with a macro, then we would need a little more information:

    1- What cells do you want to search. All cells in the current selection? All cells on a particular sheet? If so, which sheet?

    2- What sheet do you want the results on, and in what columns? Should the macro create a new sheet for the results?

    3- Are you looking for cells that contain the search string anywhere in the cell, or cells that are exactly equal to the string?

    4- What do you want to do with cells that contain formulas? Look in the formula or in the result of the formula or skip them?

    5- Do you want the case of the characters to match?
    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get 'find all' results in worksheet (xl 2002 sp2)

    Sorry for answering so late.
    (I refreshed hours long my lounge web page, and what I observed was only more and more visits on my question and still zero answer... <img src=/S/beep.gif border=0 alt=beep width=15 height=15>....I was giving up, away from the computer.)
    Well, Steve, your macro is just <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <font color=red>P E R F E C T</font color=red> <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>!!!!!!

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Lebanon/France
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Get 'find all' results in worksheet (xl 2002 sp2)

    Legare, I suppose you crossposted with sdckapr.

    His macro mimics pretty perfectly the excel menu command, using the range currently selected, considering the "find what" as a substring of the cells content searched, even allowing to check or uncheck the case matching!!! I could not believe it.
    It generates a new worksheet for the results which is fine with me.

    I took good notice on being more specific next time, as I might be not so lucky!
    Thanks for your care

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Get 'find all' results in worksheet (xl 2002 sp2)

    My macro searches the ACTIVE sheet (the WHOLE sheet, not just the selected range!). It could be "tweaked" without too much trouble to set it up any with any of Legare's questions.

    Steve

Posting Permissions

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