+ Reply to Thread
Results 1 to 3 of 3

Filter/find/isolate one specific term?

  1. #1
    williejoeshaver
    Guest

    Filter/find/isolate one specific term?

    Have a huge spreadsheet with cells populated with lots of text. Would like
    to isolate or highlight rows containing a single term, e.g., "aspirin" with
    one click or function as opposed to doing a find command and hitting
    everything one at a time. Any suggestions? Thanks.

  2. #2
    Jim Thomlinson
    Guest

    RE: Filter/find/isolate one specific term?

    You are a little vague with exacty what you want to do with the found cells.
    That being said here is some code that selects the cells containing the text
    you input. To run this select the range (row, column, range or entire sheet)
    that you want to search. Run the code. It will ask you to input the text to
    search for. It will then select the cells containing that text.

    Public Sub FindTextItems()
    Dim wks As Worksheet
    Dim rngCurrent As Range
    Dim rngFirst As Range
    Dim rngConsolidated As Range
    Dim rngToSearch As Range
    Dim strToFind As String

    strToFind = InputBox("Please enter the text to search for.", "Find Text")

    Set wks = ActiveSheet
    Set rngToSearch = Intersect(Selection, wks.UsedRange)
    Set rngCurrent = rngToSearch.Find(strToFind)

    If rngCurrent Is Nothing Then
    MsgBox strToFind & " was not found in the selected area.", _
    vbOKOnly, "Not Found"
    Else
    Set rngConsolidated = rngCurrent
    Set rngFirst = rngCurrent
    Do
    Set rngConsolidated = Union(rngCurrent, rngConsolidated)
    Set rngCurrent = rngToSearch.FindNext(rngCurrent)
    Loop Until rngCurrent.Address = rngFirst.Address
    rngConsolidated.Select
    End If
    End Sub

    --
    HTH...

    Jim Thomlinson


    "williejoeshaver" wrote:

    > Have a huge spreadsheet with cells populated with lots of text. Would like
    > to isolate or highlight rows containing a single term, e.g., "aspirin" with
    > one click or function as opposed to doing a find command and hitting
    > everything one at a time. Any suggestions? Thanks.


  3. #3
    williejoeshaver
    Guest

    RE: Filter/find/isolate one specific term?

    Thanks for the help. Sorry for being vague. Basically this is a customer
    list with customer name, acct #, and some notes in each column. We want to
    be able to pick out only the customers for whom a specific word appears in
    their notes (e.g., "this customer called about BIAXIN"). If we are able to
    highlight/isolate those customers (from the thousands who do not meet that
    single criterion), we can create an easily targeted call list. In other
    words, I want to pick the 1000 rows that contain a cell with that text from
    the 10000 rows total without searching one by one (and the term we're
    searching for is buried in lots of text so we can't simply sort).

    "Jim Thomlinson" wrote:

    > You are a little vague with exacty what you want to do with the found cells.
    > That being said here is some code that selects the cells containing the text
    > you input. To run this select the range (row, column, range or entire sheet)
    > that you want to search. Run the code. It will ask you to input the text to
    > search for. It will then select the cells containing that text.
    >
    > Public Sub FindTextItems()
    > Dim wks As Worksheet
    > Dim rngCurrent As Range
    > Dim rngFirst As Range
    > Dim rngConsolidated As Range
    > Dim rngToSearch As Range
    > Dim strToFind As String
    >
    > strToFind = InputBox("Please enter the text to search for.", "Find Text")
    >
    > Set wks = ActiveSheet
    > Set rngToSearch = Intersect(Selection, wks.UsedRange)
    > Set rngCurrent = rngToSearch.Find(strToFind)
    >
    > If rngCurrent Is Nothing Then
    > MsgBox strToFind & " was not found in the selected area.", _
    > vbOKOnly, "Not Found"
    > Else
    > Set rngConsolidated = rngCurrent
    > Set rngFirst = rngCurrent
    > Do
    > Set rngConsolidated = Union(rngCurrent, rngConsolidated)
    > Set rngCurrent = rngToSearch.FindNext(rngCurrent)
    > Loop Until rngCurrent.Address = rngFirst.Address
    > rngConsolidated.Select
    > End If
    > End Sub
    >
    > --
    > HTH...
    >
    > Jim Thomlinson
    >
    >
    > "williejoeshaver" wrote:
    >
    > > Have a huge spreadsheet with cells populated with lots of text. Would like
    > > to isolate or highlight rows containing a single term, e.g., "aspirin" with
    > > one click or function as opposed to doing a find command and hitting
    > > everything one at a time. Any suggestions? Thanks.


+ 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