Microsoft Office Forums - View Single Post

Colin Legg · 06-22-2012, 09:39 AM

When you use a With... End With block, you have to make sure you close out the block using an End With statement. Your code is missing a few of these End With statements. The easiest way to spot it is to indent your code so that the "With" and the "End With" line up.

Code:

Sub SortdataOnly()
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Print
        With .Range("Cr2:DE3980")
            .Value = .Value
        End With
 
        With .Sort
 
           'set up the sort fields for Print on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Cr2:Cr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Cs2:Cs3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for Print
            .SetRange Sheet2.Range("Cr2:De3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for BNY
        With .Range("Dq2:Eb3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on BNY on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Dq2:Dq3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Dr2:Dr3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
             'set up the rest of the sort and apply for BNY
            .SetRange Sheet2.Range("Dq2:Eb3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for Wilde
        With .Range("En2:Ey3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on Wilde on (data tab)
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("En2:En3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Eo2:Eo3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
           End With
 
            'set up the rest of the sort and apply for Wilde
            .SetRange Sheet2.Range("En2:Ey3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
    With Sheet2
 
        'get rid of any null strings stored in the cells for DS Graphics
        With .Range("Fm2:Fx3980")
            .Value = .Value
        End With
 
        With .Sort
            'set up the sort fields on DS Graphics on (data tab)
 
            With .SortFields
                .Clear
                .Add Key:=Sheet2.Range("Fm2:Fm3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
                .Add Key:=Sheet2.Range("Fn2:Fn3980"), _
                            SortOn:=xlSortOnValues, _
                            Order:=xlAscending, _
                            DataOption:=xlSortNormal
            End With
 
            'set up the rest of the sort and apply for DS Graphics
            .SetRange Sheet2.Range("Fm2:Fx3980")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlSortColumns
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
 
End Sub