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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
kacorretiredAuthor Commented:
hi  ssaqibh,

unfortunately in line 19 I got the same error message

thanks for your time

wbr kacor
0
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

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
dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Change

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

to

arow.EntireRow.Select
0
kgerbChief 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.