Code to sort data is being ignored...

wpryan

Well-known Member
Joined
May 26, 2009
Messages
534
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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?
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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):
Rich (BB code):
    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
 
Upvote 0
Solution
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):
Rich (BB code):
    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!
 
Upvote 0
You are welcome.
Glad I was able to help.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,201
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top