Run-time error '1004': Method 'Range' of object '_worksheet' failed

[Shaunch]

Hi
I have looked at the threads that have this heading but cannot see one that matches my situation and after looking at them all I am now confused
any help would be great

my code is

Private Sub cboName_Change()

Dim Found As Range
Dim str As String

str = Me.cboName.Value
Set Found = Sheet2.Range("D9", Range("D" & Rows.Count).End(xlUp)).Find(str) this is where i get the error when it searches the name selected

If Found Is Nothing Then
MsgBox ("Not Found!")
Else
Me.txtStart = Found.Row
Me.txtHours = Cells(Found.Row, 3).Value
Me.txtnumber = Cells(Found.Row, 5).Value
Me.txtDepartment = Cells(Found.Row, 6).Value
Me.txtSex = Cells(Found.Row, 7).Value
Me.txtFIREWARDEN = Cells(Found.Row, 9).Value
Me.txtFIRSTAID = Cells(Found.Row, 10).Value
Me.txtHr = Cells(Found.Row, 11).Value
Me.txtCold = Cells(Found.Row, 13).Value
End If

End Sub

 

[DacEasy]

Hi,

It seems to work, I assigned "str" a string and it found the string in Column D, see the screenshot. When or where does the "Me.cboName.Value" pull from? You may need to make "str" a global variable and assign it when "Me.cboName.Value" pulls its value.

 

[bebo021999]

Or
Dim Found as variant
???

 

[DacEasy]

Even if you change to variant, the problem below still exist:

When or where does the "Me.cboName.Value" pull from?

 

[DacEasy]

When or where you assign "Me.cboName.Value", assign "str" to it with:

Global Dim str As String
str = Me.cboName.Value

and comment out the current Dim str as string
then you can use str in that private subroutine.

 

[RoryA]

You need to qualify the second Range call with the same sheet:

Set Found = Sheet2.Range("D9", Sheet2.Range("D" & Rows.Count).End(xlUp)).Find(str)

 

[Alex Blakenburg]

Since @RoryA's solution also flushes out that Sheet2 is not the active sheet, you are going to need to qualify all your uses of "Cells"
ie

Private Sub cboName_Change()

    Dim Found As Range
    Dim str As String
   
    str = Me.cboName.Value
   
    With Sheet2
        Set Found = .Range("D9", .Range("D" & Rows.Count).End(xlUp)).Find(str)
       
        If Found Is Nothing Then
        M sgBox("Not Found!")
        Else
            Me.txtStart = Found.Row
            Me.txtHours = .Cells(Found.Row, 3).Value
            Me.txtnumber = .Cells(Found.Row, 5).Value
            Me.txtDepartment = .Cells(Found.Row, 6).Value
            Me.txtSex = .Cells(Found.Row, 7).Value
            Me.txtFIREWARDEN = .Cells(Found.Row, 9).Value
            Me.txtFIRSTAID = .Cells(Found.Row, 10).Value
            Me.txtHr = .Cells(Found.Row, 11).Value
            Me.txtCold = .Cells(Found.Row, 13).Value
        End If
    End With

End Sub

 

[Siddharth Rout]

And do not forget to also qualify the Rows.Count as well.

It is possible to get an "Application - defined or Object-defined" error if an Excel 2003 worksheet is active when you are running this code on a Excel 2007+ File. Here is a CLASSIC EXAMPLE where this error may occur.

I always advise using "." before Rows.Count and Columns.Count. As mentioned your code may fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. Similarly Columns.Count returns 256 and 16384, respectively.

It is highly advisable that you fully qualify ALL your objects. LEAVE NO MAN BEHIND, er I MEAN CODE UNQUALIFIED!

With Sheet2
    Set Found = .Range("D9", .Range("D" & .Rows.Count).End(xlUp)).Find(Str)
End With