Run Time Error '1004' and Range Class

[dawa1234]

I keep getting this error that says "Run Time Error '1004' Select Method of Range Class Failed" and if I debug it, it points to ".Range(sortRange).Select"

Public Sub Sorts()
  With Worksheets(sheetName)
     .Range(sortRange).Select
      Selection.Sort Key1:=Range(Key),
         Order1:=xlAscending, Header:=xlGuess, _
         OrderCustom:=1, MatchCase:=False,  
         Orientation:=xlTopToBottom
      Range("A7").Select
  End With
End Sub

and if I take the "." out before the statement ".Range(sortRange).Select" I get the error "Run Time Error '1004' Sort Method of Range Class Failed." If I debug this, the VBE points to "Selection.Sort..."
There are two odd behaviors with this code. First of all, I only get this error from one particular function call, and second of all, if I am stepping through the program, the error does not show up and the program executes sucessfully. Does anyone have any ideas?



dawa1234

 

[aMember]

>> and if I take the "." out before the statement ".Range
>>(sortRange).Select" I get the error "Run Time
>>Error '1004' Sort Method of Range Class Failed."
>> If I debug this, the VBE points to "Selection.Sort..."

If you take out the ".", Excel assumes the activesheet and I'm assuming that SortRange is not defined on the activesheet.

Though this might not help with the problem, why not change:
.Range(sortRange).Select
Selection.Sort Key1:=Range(Key),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom
Range("A7&quot.Select

to:
.Range(sortRange).Sort Key1:=Range(Key),
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

----------------------------
There are two odd behaviors with this code. First of all, I only get this error from one particular function call, and second of all, if I am stepping through the program, the error does not show up and the program executes sucessfully. Does anyone have any ideas?
----------------------------

Maybe...I've run into something similar. Do you have command buttons on your spreadsheet? If so, change the TakeFocusOnClick property to false.

HTH

 

[dawa1234]

aMember,
I found that as long as I didn't put the call to the sort routine in the activate procedure of a worksheet, it seemed to work fine. I think I have found a valid work around though. I do have command buttons; however, why do you say to change the TakeFocusOn click Property to false? I am not sure as though I understand what side effects this property really has.

Thanks.
dawa1234

 

[aMember]

Let me point you to:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q177527