Error 91- Object variable not set for multiple find

[quej2003]

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?

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

 

[quej2003]

bump

 

[Norie]

Where do you get the error and are you sure the problem is with copy/paste?

 

[quej2003]

Where do you get the error and are you sure the problem is with copy/paste?

ah i missed that out, its in:

        lRowEnd = ws.Cells(Rows.Count, Found.Column).End(xlUp).Row

 

[Norie]

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.

 

[Rasm]

dont you need a DOT in front of rows

 

[Norie]

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.

 

[quej2003]

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:

    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!

 

[Andrew Poulsom]

You haven't qualified the Columns and Cells with the worksheet. If you don't the ActiveSheet will be used. S try:

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)

 

[quej2003]

You haven't qualified the Columns and Cells with the worksheet. If you don't the ActiveSheet will be used. S try:

If i had a cookie and you were in close proximity i would give you one. But yes, it works now Thanks very much!