Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32

    Question Unanswered: Search for a string within a cell

    Hi,

    I need some help.

    I have a spreadsheet with about 7000 rows of data. I want to evaluate a particular cell in each row for the string "changed". If the string is found, then I want to delete the entire row and move on to the next one.

    I am using the following code:

    Code:
    Sub mySub()
        
        Range("I2").Select
        Range("I2", Range("I2").End(xlDown)).Select
        
        For Each rw In Selection.Cells
            rw.Select
            sValue = Selection
            If Application.WorksheetFunction.Search("changed", sValue) Then
                ActiveCell.EntireRow.Select
                'Selection.Delete
            End If
        Next rw
        
    End Sub
    It works if it finds "changed", but I get a run-time error if is not found.

    Any ideas?

    Thanks in advance.
    Regards,
    Rod.

    Programmer response #5 - "Even though it doesn't work, how does it feel?"

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Add a section to check whether that condition is false, something like:

    If Application.WorksheetFunction.Search("changed", sValue) <> Nothing Then
    Exit Sub

    (not tested, but the principle works)
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32
    Shades, thanks for your assistance. You are right, the principle of testing for a false condition should work, but your suggestion didn't work.

    I got a compile error: "Invalid use of object" and highlighted the keyword Nothing.

    The problem is in the worksheet function SEARCH. If the string doesn't exist within the cell, then it causes an error, not a false condition. Even FIND doesn't work.

    There has to be another way to find a string within a string without using FIND or SEARCH and not return an error.
    Regards,
    Rod.

    Programmer response #5 - "Even though it doesn't work, how does it feel?"

  4. #4
    Join Date
    Mar 2004
    Location
    Adelaide, Australia
    Posts
    32

    Smile Problem Solved!

    Found the solution with a little bit of help from MSDN\Library.

    Created a function to split the cell contents into an array, the looped through the array until it finds the search string.

    Code:
    Sub mySub()
        
        Range("I2").Select
        Range("I2", Range("I2").End(xlDown)).Select
        
        For Each rw In Selection.Cells
            rw.Select
            If fnFindWord(rw.Value, "changed") Then
                ActiveCell.EntireRow.Select
                'Selection.Delete
            End If
        Next rw
        
    End Sub
    
    Function fnFindWord(strText As String, _
        strFind As String) As Boolean
        
        Dim astrText() As String
        Dim lngCount As Long
        
        astrText = Split(strText)
        
        For lngCount = LBound(astrText) To UBound(astrText)
            If astrText(lngCount) = strFind Then
                fnFindWord = True
            Else
                fnFindWord = False
            End If
        Next
        
    End Function
    Regards,
    Rod.

    Programmer response #5 - "Even though it doesn't work, how does it feel?"

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    Good one, Rocket!
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    A fine solution lads, but a bit brutish. Try a no-code alternative:

    Insert a column prior to I
    In cell I2, type =FIND("changed")
    With pointer exactly at right-bottom of cell I2, double-click to copy to bottom
    Choose Edit, GoTo, Special
    Choose Formulas, Numbers (deselect Text, Logicals, Errors)
    Choose Edit, Delete... Entire Row
    Delete column I
    Last edited by actuary; 05-07-04 at 13:11.

Posting Permissions

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