how to fix run-time error -21473198784 (80028018) method select of object range failed ?

[Dilermando FLF]

Hello.

I have a very simple worksheet which is my appointments agenda:
- no headings
- column A appointment dates
- column B corresponding days of the week
- column C corresponding hours
- column D corresponding descriptions
- columns E to N temporarily void

Previously, this worksheet ran under Excel 2007. A very simple recorded macro enabled me to smoothless sort it by:
1 - column A, oldest to newest
2 - column C, smallest to largest

After migrating to Excel 2010, this macro runs no more. The title displays the message error I get. Upon debugging, the command line Range("A1:N40").Select is highlighted.

Will someone be willing to help me, obviously a very illiterate person in macro programming ?

Thank you very much !

Dilermando FLF


Here follows my macro:


Sub ordenaragenda()
'
' ordenaragenda Macro
'


'
Range("A1:N40").Select
ActiveWorkbook.Worksheets("CONTROLE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("CONTROLE").Sort.SortFields.Add Key:=Range("A1:A40" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("CONTROLE").Sort.SortFields.Add Key:=Range("C1:C40" _
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("CONTROLE").Sort
.SetRange Range("A1:N40")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Kyle123]

Just delete that line, it isn't needed, I suspect that this may be a little more robust

Sub ordenaragenda()
 With ActiveWorkbook.Worksheets("CONTROLE")
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:= .Range("A1:A40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Sort.SortFields.Add Key:= .Range("C1:C40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  with .Sort
    .SetRange Range("A1:N40")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
   .Apply
  End With
 
 End Sub

 

[Dilermando FLF]

Hello.
Thank you for your swift answer.
First, I deleted the line in my original macro as suggested. I ran it, but it didn' work.
Then I replaced my macro by yours (exactly as given) and I ran it. I got an error message "compile error expected End With".
Upon debugging, the last command line End Sub is highlighted.
Bye.

Just delete that line, it isn't needed, I suspect that this may be a little more robust

Sub ordenaragenda()
 With ActiveWorkbook.Worksheets("CONTROLE")
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:= .Range("A1:A40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Sort.SortFields.Add Key:= .Range("C1:C40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  with .Sort
    .SetRange Range("A1:N40")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
   .Apply
  End With
 End Sub

 

[Kyle123]

Oops, don't know how I missed that one, put "End With" just above end sub and try that

 

[Dilermando FLF]

Well, sorry, command line end with is above end sub - exactly as follows. And again the same error "expected end with" ...

Sub ordenaragenda()
With ActiveWorkbook.Worksheets("CONTROLE")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A1:A40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("C1:C40"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange Range("A1:N40")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[Andrew Poulsom]

You need two End Withs, and probably also:

.SetRange .Parent.Range("A1:N40")

 

[par60056]

There should be 2 lines that both say "End With" 1 to end "With Activeworkbook" and 1 to end "With .Sort"

 

[Dilermando FLF]

bingo !
Now with two lines end with, my macro works fine !
Thank you all for your most appreciated help !