Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

String search in Excel

Posted on 2012-03-23
12
266 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
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 166 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 167 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 167 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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…

840 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