Error 1004 (Sort method of Range class failed)

[LearningEX]

Hi all

i keep getting this error and when i run F8 it skips this part of the code and goes straight to error

.Range("s9:s10000").Sort Key1:=.Range("s9"), Order1:=xlAscending, Header:=xlNoEnd With
'reset error
   On Error GoTo 0
   Exit Sub
'if error occurs then show me exactly where the error occurs
Sortit_Error:

and this is the whole code

Sub Sortit_123()'error handler
   On Error GoTo Sortit_Error
'sort the phone list
With Sheet7
.Range("B9:r10000").Sort Key1:=.Range("r9"), Order1:=xlAscending, Header:=xlNo
'sort the index numbers
.Range("s9:s10000").Sort Key1:=.Range("s9"), Order1:=xlAscending, Header:=xlNo
End With
'reset error
   On Error GoTo 0
   Exit Sub
'if error occurs then show me exactly where the error occurs
Sortit_Error:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Sortit of Module Sortit_123"
End Sub

Can anyone help please

 

[Andrew Poulsom]

Can you perform those sort actions manually?

 

[LearningEX]

Can you perform those sort actions manually?

eventually the sheets will not be seen and everything will be done via a userform

 

[Andrew Poulsom]

That doesn't answer my question.

 

[LearningEX]

That doesn't answer my question.

i can sort my date out yes but then it throws my PayRates out of order

 

[Andrew Poulsom]

Sorry, that doesn't make an sense to me. Remember that you can see your worksheet but we can't. What happens exactly if you perform those sort actions through the user interface?

 

[LearningEX]

Sorry, that doesn't make an sense to me. Remember that you can see your worksheet but we can't. What happens exactly if you perform those sort actions through the user interface?

here is my sheet.

if you want to add a new entry open Developer and from the forms select Agency Daily Input and from the dates pick a date between the dates on the worksheet, when done select from drop down lists and the enter a start time and end time then hit add shift.

when done go back and look at the entry it should be in between the start and end dates then take a look at the Pay rate col and you will notice that they do not relate to the correct row.


https://www.dropbox.com/s/z9qkgxe2x7erg5n/for forum.xlsm?dl=0

 

[Andrew Poulsom]

In the formulas in column T on sheet Agency_Daily_Input you need to remove all references to that sheet. For example Agency_Daily_Input!D9 would become simply D9. For an explanation see:

Daily Dose of Excel » Blog Archive » Sorting Formulas with Sheet Names

 

[LearningEX]

In the formulas in column T on sheet Agency_Daily_Input you need to remove all references to that sheet. For example Agency_Daily_Input!D9 would become simply D9. For an explanation see:

Daily Dose of Excel » Blog Archive » Sorting Formulas with Sheet Names

thank you sir, once again you have sup parsed yourself as a great teacher yet again and i just did not realize that referring to the sheet like that would cause me so much of a head ache

thank you again sir