Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Run-time error '91: Object variable or with block variable not set 2

Status
Not open for further replies.
PWD

PWD

Technical User
Jul 12, 2002
823
GB
Good afternoon. I have spreadsheets with multiple worksheets and my user wants to search for certain names. I start off with this
Code: 
 MyText = InputBox("Type the text for the rows you want to cut.", "Look for this")

And get down to
Code: 
  Range("E:E").Find(What:=MyText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

The trouble is that it falls over at the second consecutive worksheet where nothing matches the find. I've tried a couple of
Code: 
 If ....Then
to trap & move on but it fails on the first loop (the first 'Find') of the second sheet where nothing matches.

Any ideas?

Many thanks,

Des.
 
Sort by date Sort by votes
Your code does not specify which sheet it's searching.

Also, you're using ActiveCell, but the active cell isn't guaranteed unless you pro grammatically activate a cell. You're also explicitly searching column E, and your code breaks if the cursor is on another column.

Try this
Code: 
Dim myRange as Range
Dim myText as String
Dim mySheet as Worksheet

myText = InputBox("Type the text for the rows you want to cut.", "Look for this")

For each mySheet in Activeworkbook.Worksheets
With mySheet
  Set myRange = .Range("E:E").Find(What:=myText,   After:=Range("E1"), LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False)

myRange.Rows.Delete
End With
Next mySheet

Also this only finds one row. If you want to capture all rows with the search text, you will need another loop, using the FindNext method

Code: 
    While Not myRange Is Nothing
        Set myRange = .Range("E:E").FindNext
        If Not myRange Is Nothing Then
            myRange.Rows.Delete
        End If
    Wend
    End With
 
Upvote 0 Downvote


I have spreadsheets with multiple worksheets and my user wants to search for certain names.
Click to expand...
Why would anyone spend all this effort coding something that your user could do and ought to do themselves, using the FIND feature in Excel?

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
OK folks here's the thing. The user wants to find a certain customer within sales details of about a dozen salesmen (each one has a separate worksheet), cut each line where the customer appears and then paste these lines back in at the bottom of each worksheet.

Code: 
Sub Search_And_Cut2()

    Dim ws As Worksheet
    
'Stops the screens displaying as they are processed
Application.ScreenUpdating = False

MyText = InputBox("Type the text for the rows you want to cut.", "Look for this")
'Creates an Input Box for user choice of 'Find'

    'For Each ws In ThisWorkbook.Sheets

    ws.Activate
    Range("A65536").Select 'Goes to the end
    Selection.End(xlUp).Select 'Goes up to last line with data
    r = ActiveCell.Row 'Sets limit for searching, i.e. row number of bottom cell
 i = 0 'Need to reset this each time it does a new WorkSheet

Range("E1").Select 'Column with data to search through
 
        Do Until ActiveCell.Row > r 'May duplicate 1st found line
i = i + 1 'How many times the 'Find' has been successful

Range("E:E").Find(What:=MyText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
            If ActiveCell.Row <= r Then 'Stops duplication of 1st found line
            
            Selection.EntireRow.Cut _
            Destination:=Cells((r + i + 5), 1) 'Puts it a bit further down starting in Column 1 - 'A'
            '(after the bottom cell plus the number of times 'Find' was successful plus 5 for luck)
            End If
            
        Loop
        
    Next ws
    
Application.ScreenUpdating = True
'Restores the screen display
        
End Sub

Now this appears to work fine until it encounters the second worksheet where the 'Find' is unsuccessful. That's when the error occurs.

Des.
 
Upvote 0 Downvote
Actually this is the code after the Input Box
Code: 
     For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    
    r = ActiveSheet.UsedRange.Rows.Count

Not that it seems to make any difference.

Des.
 
Upvote 0 Downvote



A better technique is to findnext, and use something like the previous row to compare the found row. You really don't need all these other handstands and somersaults.

But again, using the stock workbook find, actually FindAll, will get you EVERY OCCURRENCE IN THE WORKBOOK!!! Do you EALLY need VBA???

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
OK, to put it another way:- My user wants to find a certain customer within sales details of about a dozen salesmen (each one has a separate worksheet), cut each line where the customer appears and then paste these lines back in at the bottom of each worksheet. I can't seem to get 'FindAll' to run within my macros but I can't really see how that would be much of an improvement [I'm sure someone will tell me :) ] on the Do/Loop that I already have.

I have put in some error handling but this only works the first time an error occurs, i.e. there's no match on a worksheet. When the code moves on to the next worksheet and there's no match there either, it stops with the
Code: 
 Run-time error '91: Object variable or with block variable not set
message. I have tried
Code: 
 If Err.Number <> 0 Then Err.Clear
and
Code: 
 If Err.Number <> 0 Then GoTo
but neither of those seem to allow two consecutive errors within the macro.

So, everything does what my user wants it to do but it just can't handle two consecutive errors.

Des.
 
Upvote 0 Downvote
You need:
- On Error... statement,
- proper error trapping settings.
The sample code:
Code: 
On Error Resume Next
' code with possible error
If Err.Number<>0 Then
    Err.Clewr
    ' do the rest
End If
As for error trapping settings: Tools>Options, 'General' tab, 'Break on unhandled errors' set.

combo
 
Upvote 0 Downvote


Well we finally got a better statement of requirements. It's not ONLY a find, but also a REPORT: here's what we found.

Have a sheet named Report, all other sheets will be searched.
Code: 
    Dim ws As Worksheet, sFind As String, rFound As Range, lPrevRow As Long
    
    sFind = "skip"
    
    For Each ws In Worksheets
        If ws.Name <> "Report" Then
            Set rFound = ws.Cells.Find(sFind)
            If Not rFound Is Nothing Then
                GoSub ReportIt
                
                Do While lPrevRow < rFound.Row
                    Set rFound = ws.Cells.FindNext(rFound)
                    If Not rFound Is Nothing Then
                        GoSub ReportIt
                    Else
                        Exit Do
                    End If
                    
                Loop
            End If
        End If
    Next
    Exit Sub
ReportIt:
    With Sheets("Report")
        With .Cells(.[A1].CurrentRegion.Rows.Count + 1, "A")
            .Value = ws.Name
            .Offset(0, 1).Value = rFound.Value
            .Offset(0, 2).Value = rFound.Row
            .Offset(0, 3).Value = rFound.Column
        End With
    End With
    lPrevRow = rFound.Row
    Return


Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
This is what I've got in place.
Code: 
 On Error GoTo Line10 'In case there's no match
 Range("E:E").Find(What:=MyText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
            If ActiveCell.Row <= r Then 'Stops duplication of 1st found line
            
            Selection.EntireRow.Cut _
            Destination:=Cells((r + i + 5), 1) 'Puts it a bit further down starting in Column 1 - 'A'
            '(after the bottom cell plus the number of times 'Find' was successful plus 5 for luck)
            End If
            
Line10:

If Err.Number <> 0 Then Err.Clear 'Error handling

Loop

This doesn't seem to clear the error because if you put in another
Code: 
 If Err.Number <> 0 Then ....
it will trigger that 'Then' prior to activating the next worksheet.

Des.
 
Upvote 0 Downvote
ALWAYS use a Resume statement in your error handler code ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
After:
Code: 
If Err.Number <> 0 Then
    MsgBox Err.Number
    Err.Clear
    MsgBox Err.Number
End If{/code]
I have 91 and 0 displayed. What code do you have?

combo
 
Upvote 0 Downvote



This destroys your process...
Code: 
Selection.EntireRow.Cut
1) I would not be using Select & Activate!!!

2) When you CUT the row where your ActiveCell is, guess what happens to your reference? It is LOST in SPACE!

Why cut anything?

Just COPY the row and PASTE it in your destination if you want.
Code: 
ReportIt:
    With Sheets("Report")
        With .Cells(.[A1].CurrentRegion.Rows.Count + 1, "A")[b]
           rFound.entirerow.copy .cells(1,1)[/b]
        End With
    End With
    lPrevRow = rFound.Row
    Return

BTW, if you SET a range object like I demonstrated, you do not have to do the error checking if there is no match. The state of the object determins that.

Skip,

[glasses]Just traded in my old subtlety...
for a NUANCE![tongue]
 
Upvote 0 Downvote
PH, if the error source is not removed, an endless error loop will be created.

combo
 
Upvote 0 Downvote
combo, without any kind of Resume statement you'll never exit the error handler ....
 
Upvote 0 Downvote
Yes if you need to jump somewhere. Without Resume... the code will run after the error handler. Single 'Resume' will cause the code to return to the error generating line, back to the error handler and so on.

combo
 
Upvote 0 Downvote
Guys, guys!! My user came to me because she was going through each of these sheets and manually finding the required customer, cutting the line and then pasting it at the bottom of that salesman's worksheet. All I've done it to automate this process. Everything works just fine until the second time there's no match for that customer. It doesn't matter that there has been a successful 'Find' in between. I've put in the MsgBox and can see that it does clear the error but it still errors. I just created a very simple workbook with a series of a, b, c in column E on Sheets 1, 3 & 5 and just blank on Sheets 2 & 4. I just search for "a" in order to test. It errors when it doesn't find a match on Sheet 4.

I appreciate that this code isn't perfect - maybe not even optimal - but if it would do what I, and my user, want then it would certainly do.

Code: 
Sub Search_And_Cut2()

 Dim ws As Worksheet
    
'Stops the screens displaying as they are processed
Application.ScreenUpdating = False

MyText = InputBox("Type the text for the rows you want to cut.", "Look for this")
'Creates an Input Box for user choice of 'Find'

    For Each ws In ActiveWorkbook.Worksheets
    ws.Activate
    

Range("A65536").Select 'Goes to the end
Selection.End(xlUp).Select 'Goes up to last line with data
r = ActiveCell.Row 'Sets limit for searching, i.e. row number of bottom cell

i = 0 'Need to reset this each time it does a new WorkSheet

Range("E1").Select 'Column with data to search through
 
        Do Until ActiveCell.Row > r 'May duplicate 1st found line
i = i + 1 'How many times the 'Find' has been successful

If i > r Then GoTo Line20

On Error GoTo Line10 'In case there's no match

 Range("E:E").Find(What:=MyText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
            If ActiveCell.Row <= r Then 'Stops duplication of 1st found line
            
            Selection.EntireRow.Cut _
            Destination:=Cells((r + i + 5), 1) 'Puts it a bit further down starting in Column 1 - 'A'
            '(after the bottom cell plus the number of times 'Find' was successful plus 5 for luck)
            End If
            
Line10:

If Err.Number <> 0 Then
    MsgBox Err.Number
    Err.Clear 'Error handling
    MsgBox Err.Number
End If
        Loop
        
Line20:
        
    Next ws
    
Application.ScreenUpdating = True
'Restores the screen display
        
End Sub

It's driving me NUTS!!!!!!!

Des.
 
 http://www.mediafire.com/file/5sdxnws6hod25wc/SKB Test.xls
Upvote 0 Downvote
Again, use the Resume statement:
Code: 
...
Line10:
    If Err.Number <> 0 Then
      Err.Clear
      Resume Line20
    End If
  Loop
Line20:
Next ws
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
You little beauty!!!! I've never used the 'Resume' so didn't appreciate where/how to use it. Maximum respect. Many thanks - to everyone.

Des.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
1K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
942
morechocolate
morechocolate
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
698
sxschech
sxschech
feipezi
  • Locked
  • Question
Hoe to fix run time error 2147217900 (80040e14) syntax error in Order By clause 1
Replies
6
Views
1K
Andrzejek
Andrzejek
EliseFreedman
  • Locked
  • Question
booking tool - all day time slots
Replies
3
Views
546
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top