| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
05-04-04, 22:37
|
|
Registered User
|
|
Join Date: Mar 2004
Location: Adelaide, Australia
Posts: 32
|
|
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?"
|
|
05-05-04, 09:37
|
|
Registered User
|
|
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)
|
|
05-05-04, 19:21
|
|
Registered User
|
|
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?"
|
|
05-05-04, 21:49
|
|
Registered User
|
|
Join Date: Mar 2004
Location: Adelaide, Australia
Posts: 32
|
|
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?"
|
|
05-06-04, 09:23
|
|
Registered User
|
|
Join Date: Oct 2003
Posts: 1,091
|
|
|
|
05-07-04, 12:07
|
|
Registered User
|
|
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 12:11.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Search for a string within a cell
