Results 1 to 6 of 6

Using sort descending in VBA

Threaded View

  1. #3
    Forum Contributor
    Join Date
    03-18-2009
    Location
    Tokyo, Japan
    MS-Off Ver
    Excel 2003
    Posts
    104

    Re: Using sort descending in VBA

    I used the macro recorder and inserted the recorded code into the macro I'm using. I'm getting an error on the .sort section of the code stating "method or data member" not found. I'm not sure of the syntax to point to the current worksheet either. Thanks for your patience with me on this. Edit: I removed the (s) from the worksheet object and the code runs but it isn't sorting the data.

    
    
    Sub x()
    
    Dim n As Long, ws As Worksheet
    
    
      Dim lrow As Long, sh As Worksheet
        Application.ScreenUpdating = False
        On Error Resume Next
    
    worksheetcount = ActiveWorkbook.Worksheets.Count
       
    beginningrow = worksheetcount - 2
    
    For i = beginningrow To Worksheets.Count
        With Worksheets(i)
    
            n = .Range("Q" & Rows.Count).End(xlUp).Row
            .Range("S2:S" & n).Formula = "=(Q2-T2)/V2"
            .Range("T2:T" & n).Formula = "=AVERAGE(Q$2:Q$" & n & ")"
            .Range("U2:U" & n).Formula = "=MEDIAN(Q$2:Q$" & n & ")"
            .Range("V2:V" & n).Formula = "=STDEV(Q$2:Q$" & n & ")"
            .Range("W2:W" & n).Formula = "=SKEW(Q$2:Q$" & n & ")"
            .Range("T2:W" & n).Value = .Range("T2:W" & n).Value
        End With
        
        Columns("S:S").Select
        ActiveWorkbook.Worksheet.sort.SortFields.Clear
        ActiveWorkbook.Worksheet.sort.SortFields.Add Key:= _
            Range("S1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheet.sort
            .SetRange Range("A2:W11")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    Next i
    
    
    End Sub
    Last edited by eonizuka; 01-05-2010 at 01:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1