Help! "Run-time error 438: Object doesn't support this property or method"

[raeannb]

Hello! I think I might be doing something dumb because my code isn't that complicated, but...I keep getting a run-time error on the following bit:

With WB1.Sheets(1)
    'Count number of rows:
    x = .Range("A1048576").End(xlUp).Row
    'Find Test Name column:
    TestCol = .Rows(5).Find(What:="Test.Name", LookAt:=xlPart, SearchOrder:=xlByColumns).Column
    'Sort:
    With .Sort
        .SortFields.Clear
        .SortFields.Add Key:=.Range(.Cells(6, TestCol), .Cells(x, TestCol)), SortOn:=xlSortOnValues, _ 
        Order:=xlAscending, DataOption:=xlSortNormal **** Error occurs here! ****
 
        .SetRange .Range(.Cells(5, 1), .Cells(x, .Range("XFD5").End(xlToLeft).Column)) 
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With

I've searched online and a bunch of the advice for this error goes right over my head. Any help would be greatly appreciated. Thank you!

 

[HalfAce]

What version of excel are you using?
That code looks to be from a version newer than 03 (what I'm on here).

I would first try putting an apostrophe between: xlAscending and the comma that follows it.
( Order:=xlAscending ', DataOption:=xlSortNormal )

If this doesn't do it, then I'd move that apostrophe to the left to comment out the next argument (and keep doing this) until I find the one my version doesn't like.

(Does that help?)

 

[raeannb]

I was being dumb! I confused my with statements. Problems fixed

 

[HalfAce]

Hey, glad I could help!

 

[Norie]

The problem is the nested withs.

Specifically .Range, .Cells etc were referring back to .Sort not the worksheet.

Try this:

 Set ws1 = WB1.Worksheets(1)
 
    'Count number of rows:
    x = ws1.Range("A1048576").End(xlUp).Row
    'Find Test Name column:
    testcol = ws1.Rows(5).Find(What:="Test.Name", LookAt:=xlPart, SearchOrder:=xlByColumns).Column
    'Sort:
    With ws1.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws1.Range(ws1.Cells(6, testcol), ws1.Cells(x, testcol)), SortOn:=xlSortOnValues, _
                        Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange ws1.Range(ws1.Cells(5, 1), ws1.Cells(x, ws1.Range("XFD5").End(xlToLeft).Column))
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

This will work but there are probably better ways to do it.


For example you could set the ranges for the key and data(?) outside the 'Sort' With.

    With ws1
        Set rngKey = .Range(.Cells(6, testcol), .Cells(x, testcol))
        Set rngData = .Range(.Cells(5, 1), .Cells(x, .Range("XFD5").End(xlToLeft).Column))
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=rngKey, SortOn:=xlSortOnValues, _
                            Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange rngData
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With

 

[HalfAce]

Good eye Norie, good eye!

I didn't even see that.

 

[Norie]

It's probably because it's happened to me.

I blamed all those new fangled arguments, methods etc and still do.