Use named range in a macro in Excel vb

[Peter Kinross]

I recorded a macro to sort part of an Excel sheet.
Trouble is it doesn't use the named ranges that I selected during the recording.
"K6:K288" should refer to "%age Selctd"
"J6:J288" should refer to "PK's Score"
"A5:AL288" should refer to "Db".
But substituting these names for the range doesn't work. How do I do it?

Code: Select all

Sub SortDb()
    Application.Goto Reference:="Db"
    ActiveWorkbook.Worksheets("CompareFundsAndModels-Super_2St").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("CompareFundsAndModels-Super_2St").Sort.SortFields. _
        Add Key:=Range("K6:K288"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("CompareFundsAndModels-Super_2St").Sort.SortFields. _
        Add Key:=Range("J6:J288"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("CompareFundsAndModels-Super_2St").Sort
        .SetRange Range("A5:AL288")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

[HansV]

Does this work?

Code: Select all

Sub SortDb()
    With Worksheets("CompareFundsAndModels-Super_2St")
        .Range("%age Selctd").Sort _
            Key1:=.Range("PK's Score"), Order1:=xlDescending, _
            Key2:=.Range("Db"), Order2:=xlDescending, _
            Header:=xlYes
    End With
End Sub

[Peter Kinross]

Thanks Hans.
I got "Application-defined or Object-defined error". I then put in the error handler to see if I got a different error, but it was the same, DUH!
I did alter the ranges as below, but can't see that that would cause an error.

Code: Select all

Sub SortDb()
' Keyboard Shortcut: Ctrl+t
On Error GoTo Err_SortDb
With Worksheets("CompareFundsAndModels-Super_2St")
    .Range("Db").Sort _
        Key1:=.Range("%age Selctd"), Order1:=xlDescending, _
        Key2:=.Range("PK's Score"), Order2:=xlDescending, _
        Header:=xlYes
End With
Exit_SortDb:
Exit Sub

Err_SortDb:
MsgBox Err.Description
Resume Exit_SortDb
Resume
End Sub

[HansV]

Can you post a screenshot of the Name Manager dialog displaying the definition of the named ranges?

[Peter Kinross]

Yep you hit the nail on the head (should have bee n my head). I had the names wrong, I used column heading not the actual range names.
It now works a treat.

ThanksHansStamp.gif