+ Reply to Thread
Results 1 to 4 of 4

Compile Error while sorting with VBA

Hybrid View

  1. #1
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Compile Error while sorting with VBA

    I am trying to sort data in a worksheet based on two criteria in two columns that are not adjacent, but I keep getting the error "Expected End With" but End With is in the macro

    Option Explicit
    
    Sub tester()
      Dim wks         As Worksheet
      Dim iRow        As Long
    
        For Each wks In ActiveWorkbook.Worksheets
            With wks
                With .Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range("R:R"), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("A:A"), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SetRange wks.Range("A:V")
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .Apply
         End With
     End Sub
    Attached is a sample worksheet
    Attached Files Attached Files
    Last edited by capnhud; 08-15-2011 at 01:21 PM.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Compile Error while sorting with VBA

    You forgot an End With and a Next wks. Try
    Sub tester()
      Dim wks         As Worksheet
      Dim iRow        As Long
    
        For Each wks In ActiveWorkbook.Worksheets
            With wks
                With .Sort
                    .SortFields.Clear
                    .SortFields.Add Key:=Range("R:R"), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SortFields.Add Key:=Range("A:A"), _
                                    SortOn:=xlSortOnValues, _
                                    Order:=xlAscending, _
                                    DataOption:=xlSortNormal
                    .SetRange wks.Range("A:V")
                    .Header = xlYes
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .Apply
                End With
            End With
         Next wks
     End Sub
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

  3. #3
    Registered User
    Join Date
    06-04-2007
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Compile Error while sorting with VBA

    Thank you for catching my mistake
    Last edited by shg; 08-15-2011 at 01:44 PM. Reason: deleted spurious quote

  4. #4
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: Compile Error while sorting with VBA

    You're welcome!

+ Reply to Thread

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