?
Solved

String search in Excel

Posted on 2012-03-23
12
Medium Priority
?
276 Views
Last Modified: 2012-03-23
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
0
Comment
Question by:kacor
  • 3
  • 3
  • 3
  • +1
12 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37758672
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
 
LVL 12

Expert Comment

by:kgerb
ID: 37758683
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
 
LVL 10

Author Comment

by:kacor
ID: 37758834
hi  ssaqibh,

unfortunately in line 19 I got the same error message

thanks for your time

wbr kacor
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:kgerb
ID: 37758854
You should try using .Select instead of .Activate

Kyle
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37758858
You need to activate the sheet. Add

.activate

after the line

    With Worksheets("Output").Range(Cells(1, 1), Cells(LastRow, 1))
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37758881
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
 
LVL 10

Author Comment

by:kacor
ID: 37758886
sorry  ssaqibh,

my mistake, the string was bad.

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

kacor
0
 
LVL 42

Accepted Solution

by:
dlmille earned 664 total points
ID: 37758898
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
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 668 total points
ID: 37758904
Change

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

to

arow.EntireRow.Select
0
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 668 total points
ID: 37758918
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
 
LVL 42

Expert Comment

by:dlmille
ID: 37758950
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
 
LVL 10

Author Closing Comment

by:kacor
ID: 37759503
The solutions are equal good!!

thanks again guys!!

wbr Janos alias kacor
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question