Solved

String search in Excel

Posted on 2012-03-23
12
261 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
Comment Utility
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
Comment Utility
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
Comment Utility
hi  ssaqibh,

unfortunately in line 19 I got the same error message

thanks for your time

wbr kacor
0
 
LVL 12

Expert Comment

by:kgerb
Comment Utility
You should try using .Select instead of .Activate

Kyle
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
You need to activate the sheet. Add

.activate

after the line

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

Expert Comment

by:dlmille
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 10

Author Comment

by:kacor
Comment Utility
sorry  ssaqibh,

my mistake, the string was bad.

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

kacor
0
 
LVL 41

Accepted Solution

by:
dlmille earned 166 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 41

Expert Comment

by:dlmille
Comment Utility
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
Comment Utility
The solutions are equal good!!

thanks again guys!!

wbr Janos alias kacor
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now