Error 91- Object variable not set for multiple find

quej2003

New Member
Joined
Oct 5, 2010
Messages
49
Got the following code below that I had planned to use to copy some data from one tab (comes in tab like jan 2011.txt, etc.) to another, based off of multiple finds of a few identifiers, getting the column and then doing my copy n paste. With just one identifier it works fine, without the copy n paste i could get my msgbox to give the right column with multiple finds, together no joy- giving the Error 91 (Object variable not set), have tried looking thro and understanding the help files but to no avail, would appreciate a helping hand?

Code:
Sub Copy_Data_Test_Two()
Dim ws As Worksheet, N As String, wsN As Worksheet
Dim Found As Range, lRowEnd As Integer

For Each ws In ThisWorkbook.Worksheets
    If Right(ws.Name, 4) = ".txt" Then
        N = "TEST ONLY- " & Left(ws.Name, Len(ws.Name) - 4)
        If SheetExists(N) = False Then Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = N
        Set wsN = ActiveWorkbook.Sheets(N)
 
    Set Found = Columns("AA:AZ").Find(what:="SL", LookIn:=xlValues, lookat:=xlPart)
        If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="CA", LookIn:=xlValues, lookat:=xlPart)
            If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="PT", LookIn:=xlValues, lookat:=xlPart)
                If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(what:="SZ", LookIn:=xlValues, lookat:=xlPart)
                           
        'MsgBox "found is in: " & Found.Column
                           
        lRowEnd = ws.Cells(Rows.Count, Found.Column).End(xlUp).Row
        
        ws.Select
        ws.Range(Cells(1, Found.Column - 3), Cells(lRowEnd, Found.Column)).Copy
        
        wsN.Select
        ActiveSheet.Paste Destination:=Worksheets(N).Range("A2")
            
    End If
       
Next ws
   
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Where do you get the error and are you sure the problem is with copy/paste?
 
Upvote 0
Well I think that means the problem is with the finds and not the copy/paste.

Have you tried using all the arguments for Find?

For example you aren't using After to specify where the search should begin.

Usually that isn't a problem but I'm sure I've had problems with multiple searchs over the same range.

I never could quite pinpoint what the problem was but when I added the After argument for each Find everything worked fine.

Worth a try perhaps.:)
 
Upvote 0
Rasm

Rows.Count is used to return the number of rows in a worksheet.

So Cells(Rows.Count, Found.Column) refers to the last row of the column.

You could use a very large no, eg 65000, but it's easier to use Rows.Count.

It can also help with compatibility, different versions of Excel have a different no of rows.
 
Upvote 0
Well I think that means the problem is with the finds and not the copy/paste.

Have you tried using all the arguments for Find?

For example you aren't using After to specify where the search should begin.

Usually that isn't a problem but I'm sure I've had problems with multiple searchs over the same range.

I never could quite pinpoint what the problem was but when I added the After argument for each Find everything worked fine.

Worth a try perhaps.:)

hmm still no joy when I add in the After, added in a load more also such as searchorder, searchdirection, etc. am totally stumped to be honest. Though I do think I know whats the issue, when i do use the after as below:

Code:
    Set Found = Columns("AA:AZ").Find(What:="SH", After:=Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
        If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(What:="CALL", After:=Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
            If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(What:="PUT", After:=Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
                If Found Is Nothing Then Set Found = Columns("AA:AZ").Find(What:="PRN", After:=Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)

Using that along with the rest for just one tab (that is to say 1 tab with .txt at the end) it works fine!

But if i add a second tab with .txt i again get the error 91 on the lRowEnd line, so I guess it is something to do with my For ... Next

Thanks for the help so far, shall keep trying!
 
Upvote 0
You haven't qualified the Columns and Cells with the worksheet. If you don't the ActiveSheet will be used. S try:

Rich (BB code):
Set Found = ws.Columns("AA:AZ").Find(What:="SH", After:=ws.Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
        If Found Is Nothing Then Set Found = ws.Columns("AA:AZ").Find(What:="CALL", After:=ws.Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
            If Found Is Nothing Then Set Found = ws.Columns("AA:AZ").Find(What:="PUT", After:=ws.Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
                If Found Is Nothing Then Set Found = ws.Columns("AA:AZ").Find(What:="PRN", After:=ws.Cells(1, 27), LookIn:=xlValues, LookAt:=xlPart)
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top