Sorting variable Ranges using other Ranges as Criteria in VBA

Question

I have fund names in columns of one sheet("All") and weekly fund returns in the corresponding columns of other sheet("EDITED"). I defined fund names and returns as ranges and tried to sort names using returns as key in VBA. My objective is to sort every column since each column represents another week. My code is down below. Thanks in advance.

Sub Sortmydata()

Dim rng As Range

Dim keyrng As Range


For i = 5 To 385


    Set rng = Worksheets("All").Range(Cells(3, i), Cells(385, i))
    Set keyrng = Worksheets("EDITED").Range(Cells(3, i), Cells(385, i))

    rng.Select

    Selection.Sort key1:=keyrng, _
    order1:=xlAscending, Header:=xlNo

    i = i + 1

Next i


End Sub

score 0 · Answer 1 · answered Oct 27 '17 at 12:32

0

Remove i = i + 1 from your code, the for loop increments automatically.

With the i = i + 1 your skip a column.

answered Oct 27 '17 at 12:32

Vityata

42,633
8
55
100

I did it yet my major problem is with the ranges I defined. As the code moves to "keyrng" range it hits an error. I thought I could set two ranges in two different sheets in same workbook. and use one of them as key to sort the other one. Is this possible? – skatip Oct 27 '17 at 12:47
No. Key must be in sort range What I think you want to do is to sort the column in the order of teh other column - which is a "custom sort order" https://stackoverflow.com/questions/6100944/code-an-excel-vba-sort-with-a-custom-order-and-a-value-containing-commas – Harassed Dad Oct 27 '17 at 15:52
Yes thats what I wanted to do. I checked the link you sent butI dont know if I understand it.( I need to understand it to apply it to my data :)) So, in this case I am thinking of adding more codes to create a pseudo data in some other range and sort there to copy sorted data to wherever I wanted at the beginning. Thanks. – skatip Oct 28 '17 at 19:50

score 0 · Accepted Answer · answered Nov 01 '17 at 13:30

Hi I figured sorting can only be made in same range with key values. So I created a pseudo range with the values I want to sort and my key values. Sorted in the pseudo range and copy-paste into range where I wanted to. Thanks,

Sub Sort2()

Dim ws As Worksheet Dim keyrange As Range Dim sortrange As Range

For i = 5 To 254

 Worksheets("Tier2").Activate

Set sortrange = Range(Cells(3, i), Cells(45, i))

sortrange.Copy
Range("IW3").PasteSpecial xlPasteValues




Worksheets("EDITEDTier2").Activate

Set keyrange = Range(Cells(3, i), Cells(45, i))

keyrange.Copy

Worksheets("Tier2").Activate

Range("IX3").PasteSpecial xlPasteValues

Range("IW3:IX45").Sort Key1:=Range("IX3:IX45"), _
    order1:=xlDescending, Header:=xlNo

Range("IW3:IW45").Copy

sortrange.PasteSpecial xlPasteValues

Next i

End Sub

Sorting variable Ranges using other Ranges as Criteria in VBA

2 Answers2