Range Problem - Worksheet Failed

[manzetti]

Here's what I'm doing... I have a combo box (called ClientListBox) that displays 2 columns, Column A (which is a unique id) & Column B (client's name) when they select the item, the click event should populate all the fields on the user form. Here's the code:

Private Sub ClientComboBox_Change()
Dim strFind
Dim xSearch As Range
Set xSearch = Sheets("ClientSpreadsheet").Range("a2", Range("a65536").End(xlUp))
Dim f As Integer
strFind = Me.ClientComboBox.Value
If strFind <> "" Then
With xSearch
Set C = .Find(strFind, LookIn:=xlValues)
If Not C Is Nothing Then
C.Select
With Me 'load entry to form
Me.text1.Value = ...
Me.text2.Value = ...
Me.text3.Value = ...
End With
End If
End With
Else
MsgBox "Select a Client"
End If
End Sub

It worked initially, now all I ever get is runtime error 1004 - RANGE OF OBJECT WORKSHEET FAILED.

When I walk through my code it fails at

Set xSearch = Sheets("ClientSpreadsheet").Range("a2", Range("a65536").End(xlUp))

I could use another pair of eyes. What I am I doing wrong?

 

[Sal Paradise]

Try:

Set xSearch = Sheets("ClientSpreadsheet").Range("A2:A" & Range("a65536").End(xlUp).Row)

If it doesn't work, then we'll look deeper.

 

[Legacy 68668]

With Sheets("ClientSpreadsheet")
    Set xSearch = .Range("a2", .Range("a65536").End(xlUp))
End With

 

[manzetti]

Runtime 1004

Select Method of Range Class Failed.

Debug Points to:

C.Select

With Sheets("ClientSpreadsheet")
    Set xSearch = .Range("a2", .Range("a65536").End(xlUp))
End With

 

[manzetti]

Runtime 1004

Select Method of Range Class Failed.

Debug Points to:

C.Select

Try:

Set xSearch = Sheets("ClientSpreadsheet").Range("A2:A" & Range("a65536").End(xlUp).Row)

If it doesn't work, then we'll look deeper.

 

[manzetti]

I started over i.e. created a new spreadsheet and copied over the userform and modules. I'm no longer get this error and everything works.

Thanks for your responses.