String search in Excel

Hi Experts,

I'd like to search and delete not needed parts in a text file. Using this macro I get the following error message:

Run-time error '91'
Object variable or With block variable not set

I tried to use the shorter form (Cells.Find(What:=AString).Activate) but the result was the same.

Please help me. Any suggestions appreciated.

wbr
kacor
FindString.txt
LVL 10
kacorretiredAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
sorry, I was editing...  You also need to use . prefix on your range:

Sub FindString()


Dim ARow As Integer
Dim LastRow As Integer
Dim AString As String

    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    LastRow = ActiveCell.Row
    
    With Worksheets("Output").Range(Cells(1, 1), Cells(LastRow, 1))
        .Activate
        .Range("A1").Select
        AString = "text"
        .Cells.Find(What:=AString, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        'Cells.Find(What:=AString).Activate
        ARow = ActiveCell.Row
        .Range("A1", Cells(ARow - 1, 1)).EntireRow.Select
        Selection.Delete
     
    End With
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Try this

Sub FindString()


Dim ARow As Integer
Dim LastRow As Integer
Dim AString As String
Dim found As Range
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    LastRow = ActiveCell.Row
    
    With Worksheets("Output").Range(Cells(1, 1), Cells(LastRow, 1))
        Range("A1").Select
        AString = "text"
        Set found = Cells.Find(What:=AString, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        'Cells.Find(What:=AString).Activate
        found.Activate
        ARow = ActiveCell.Row
        Range("A1", Cells(ARow - 1, 1)).EntireRow.Select
        Selection.Delete
     
    End With
End Sub

Open in new window

0
 
kgerbChief EngineerCommented:
Hello kacor,
Try this one.  I took the liberty to shorten it a little bit.  Make sure it still has your desired functionality.  As always when using code that deletes data make a copy of your data and test it on that first.

Sub FindString()
Dim AString As String, r As Range
AString = "text"
Set r = Cells.Find(What:=AString, After:=[A1], LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
    MatchCase:=False, SearchFormat:=False)
If Not r Is Nothing Then Range("A1", r).EntireRow.Delete
End Sub

Open in new window

Kyle
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
kacorretiredAuthor Commented:
hi  ssaqibh,

unfortunately in line 19 I got the same error message

thanks for your time

wbr kacor
0
 
kgerbChief EngineerCommented:
You should try using .Select instead of .Activate

Kyle
0
 
Saqib Husain, SyedEngineerCommented:
You need to activate the sheet. Add

.activate

after the line

    With Worksheets("Output").Range(Cells(1, 1), Cells(LastRow, 1))
0
 
dlmilleCommented:
This caught my attention (from original post).  I think you need to use the . Prefix before cells.find.

You're doing with worksheet but you didn't prefix the find with

.Cells.Find

you were using

Cells.Find

And also I think I agree with the others, the .Activate is not possible if you're not on the active workbook/sheet.

See amended code and advise:


Sub FindString()


Dim ARow As Integer
Dim LastRow As Integer
Dim AString As String

    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
    LastRow = ActiveCell.Row
    
    With Worksheets("Output").Range(Cells(1, 1), Cells(LastRow, 1))
        Range("A1").Select
        AString = "text"
        .Cells.Find(What:=AString, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate
        'Cells.Find(What:=AString).Activate
        ARow = ActiveCell.Row
        Range("A1", Cells(ARow - 1, 1)).EntireRow.Select
        Selection.Delete
     
    End With
End Sub

Open in new window

0
 
kacorretiredAuthor Commented:
sorry  ssaqibh,

my mistake, the string was bad.

this deletes the previous lines but the line containing the string not.

kacor
0
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Change

Range("A1", Cells(ARow - 1, 1)).EntireRow.Select

to

arow.EntireRow.Select
0
 
kgerbConnect With a Mentor Chief EngineerCommented:
kacor,
I still think you can do everything you want in just a few lines.  There is quite a bit of extra stuff in your original macro.
Sub FindString2()
Dim AString As String, r As Range
AString = "text"
With Sheets("Output")
    .Activate
    Set r = .Cells.Find(What:=AString, After:=[A1], LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not r Is Nothing Then Range("A1", r).EntireRow.Delete
End With
End Sub

Open in new window

Kyle
0
 
dlmilleCommented:
Not for points....

Sorry I jumped in as it looks like you have this well in hand.  I was just commenting on the glaring ommissions/disconnect with the original WITH statement.

I'm bowing out of this one.
0
 
kacorretiredAuthor Commented:
The solutions are equal good!!

thanks again guys!!

wbr Janos alias kacor
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.