Code to sort data is being ignored...

[wpryan]

Hi All, I am working on a document where I want to sort some data, which copies data from one worksheet to another, applies some formulas to cells, and then I want to sort the data by column A, from low to high. I got it working at some point (used the Macro recorder), but I want to apply the same kind of filter to the next two adjacent columns. After I copied and modified the code to the next location, I started getting intermittent Runtime 1004 errors, or the code is not working. I can't figure out why.
The code is:

shAllPositions.Range("B2:B722").FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-1],ImportData!R2C2:R39C3,2,FALSE)),"""",VLOOKUP(RC[-1],ImportData!R2C2:R39C3,2,FALSE))"
    shAllPositions.Range("B2:B722").Value = shAllPositions.Range("B2:B722").Value
    shAllPositions.Range("A2") = Round(shDataImport.Range("B40"), 0)
    shAllPositions.Range("A3:A722").FormulaR1C1 = "=IF(R[-1]C+1=720,1,R[-1]C+1)"
    shAllPositions.Range("A3:A722").Value = shAllPositions.Range("A3:A722").Value
    shAllPositions.Range("A2:A722").NumberFormat = "0"
    
    With shAllPositions.Sort
        .SetRange Range("A1:B722")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

...any suggestions?

 

[Joe4]

You seem to have missed something when copying over your Sort code. You need a section before your you "With" that actually tells VBA what column you are sorting by (nowhere in your code are you telling VBA what column to sort by).

When I recorded myself sorting, this is the code I got (note the part in red):

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C5") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[wpryan]

You seem to have missed something when copying over your Sort code. You need a section before your you "With" that actually tells VBA what column you are sorting by (nowhere in your code are you telling VBA what column to sort by).

When I recorded myself sorting, this is the code I got (note the part in red):

    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("C2:C5") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:C5")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

That was the trick! Thanks a lot!

 

[Joe4]

You are welcome.
Glad I was able to help.