Run-time error '91' while using Find

[Cummins]

The code continues beyond what I put in here but it is not important. When the code reaches the dataobj.range line of code, I get this error. Any ideas why it is happening?

Sub stacknew()

Dim dataobj As Object
Dim start
Dim newstart
Dim finish
Dim stackx
Dim X As Double
Dim c As Double
Dim i As Double
Dim keepx
Dim nsheet
Dim Data As String


'printobj.Activate

Set dataobj = ActiveWorkbook.ActiveSheet

'goes to the specified subroutines and identifies the last row and column in the sheet
X = lastrowN()
c = lastcolN()

'This loop is designed to be flexible with changes to the columns leading the raw data.
'So that the leadig column is always the column with the "SD-1" header
Data = "SD-1"
start = 1
'For p = 1 To c
' If dataobj.Cells(1, p) <> Data Then p = p + 1

dataobj.Range(Cells(1, 1), Cells(1, c)).Find("SD-1", LookIn:=xlFormulas, LookAt:=xlWhole).Activate
If Err.Number = 0 Then
FTemp = dataobj.Cells(ActiveCell.Row, DataCol).Address
Else
FTemp = "???"
End If

 

[AlphaFrog]

If it doesn't find a match, you cant .Activate the .Find result and it generates an error.

This will not generate an error.

Sub stacknew()

    Dim dataobj As Object
    Dim start
    Dim newstart
    Dim finish
    Dim stackx
    Dim X      As Double
    Dim c      As Double
    Dim i      As Double
    Dim keepx
    Dim nsheet
    Dim Data   As String
    [COLOR="Red"]Dim Found  As Range[/COLOR]


    'printobj.Activate

    Set dataobj = ActiveWorkbook.ActiveSheet

    'goes to the specified subroutines and identifies the last row and column in the sheet
    X = lastrowN()
    c = lastcolN()

    'This loop is designed to be flexible with changes to the columns leading the raw data.
    'So that the leadig column is always the column with the "SD-1" header
    Data = "SD-1"
    start = 1
    'For p = 1 To c
    ' If dataobj.Cells(1, p) <> Data Then p = p + 1

    [COLOR="Red"]Set Found = dataobj.Range(dataobj.Cells(1, 1), dataobj.Cells(1, c)).Find("SD-1", LookIn:=xlFormulas, LookAt:=xlWhole)
    If Not Found Is Nothing Then[/COLOR]
        FTemp = [COLOR="Red"]Found.Address[/COLOR]
    Else
        FTemp = "???"
    End If

 

[Jonmo1]

When using the syntax Range(Cells(..),Cells(..))
And you specify the Sheet on the Range, you also need to specify the sheet on both Cells..

Try

dataobj.Range(dataobj.Cells(1, 1), dataobj.Cells(1, c))

 

[Leith Ross]

Hello Cummins,

Generally speaking, it is a good practice to avoid generating errors. The range Find method will return either a range, when successful, or the special object type Nothing when it fails. The error can be avoided by setting the results of the Find to an object variable and then testing it.

Dim myRng AS Range

Set myRng = dataobj.Range(Cells(1, 1), Cells(1, c)).Find("SD-1", LookIn:=xlFormulas, LookAt:=xlWhole)
If Not myRng  Is Nothing Then
   FTemp = dataobj.Cells(ActiveCell.Row, DataCol).Address
Else
   FTemp = "???"
End If

Sincerely,
Leith Ross

 

[Jonmo1]

Try this, including the range qualificatins I mentioned earlier, and the If myRng Is Nothing by Leith

Dim myRng AS Range
 
On Error Resume Next
Set myRng = dataobj.Range(dataobj.Cells(1, 1), dataobj.Cells(1, c)).Find("SD-1", LookIn:=xlFormulas, LookAt:=xlWhole)
On Error Goto 0
 
If Not myRng  Is Nothing Then
   FTemp = dataobj.Cells(ActiveCell.Row, DataCol).Address
Else
   FTemp = "???"
End If

 

[AlphaFrog]

Try this, including the range qualificatins I mentioned earlier, and the If myRng Is Nothing by Leith

Dim myRng AS Range
 
On Error Resume Next
Set myRng = dataobj.Range(dataobj.Cells(1, 1), dataobj.Cells(1, c)).Find("SD-1", LookIn:=xlFormulas, LookAt:=xlWhole)
On Error Goto 0
 
If Not myRng  Is Nothing Then
   FTemp = dataobj.Cells(ActiveCell.Row, DataCol).Address
Else
   FTemp = "???"
End If

Shouldn't it be...
FTemp = dataobj.Cells(myRng.Row, DataCol).Address

 

[Cummins]

Thanks for all your inputs...I have used this same line in another section without this error.
The sheet clearly has a cell in column K Row 1 with "SD-1" in the cell.
I inserted your suggestions and Myrng comes back with Nothing...

 

[Jonmo1]

Well, best i can tell is the error means the Find function didn't find what it was looking for.

What is the value of the variable C at the time of the error?
What happens if you change
LookAt:=xlWhole
to
LookAt:=xlPart

 

[Cummins]

I sincerely apologize for wasting everyone's time. Something to do with tired eyes...forests and trees and such. I ran a For next loop to look for the cell and still didn't find it. Then I looked closer and realized that it was supposed to be looking for SD1-1, not SD-1. In the process of cleaning up my code now and retrieving my thumb from an orifice.
Many thanks to you!
Clark