How to create lists for custom sort in VBA

[Waimea]

Hi, I am currently using custom sort to sort a big list.

I want to apply the custom sort 39 or n times.

Sub SortCity2()
  
range("A42:H81").Select


ActiveWorkbook.worksheets("Snabb2").Sort.SortFields.Clear


ActiveWorkbook.worksheets("Snabb2").Sort.SortFields.add key:=range("A42"), SortOn:=xlSortOnValues, _
    Order:=xlAscending, CustomOrder:="City2, City1, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40", DataOption:=xlSortNormal
    
With ActiveWorkbook.worksheets("Snabb2").Sort
.SetRange range("A42:H81")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

The sorted list contains 40 * 40 cities for a total of 1600 rows + 1 header row.

The list starts with City1 to City40, then it starts over at City2, City1, City3, ..., City40.

City1, City2, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City2, City1, City3, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City3, City1, City2, City4, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

City4, City1, City2, City3, City5, City6, City7, City8, City9, City10", City11, City12, City13, City14, City15, City16, City17, City18, City19, City20, City21, City22, City23, City24, City25, City26, City27, City28, City29, City30, City31, City32, City33, City34, City35, City36, City37, City38, City39, City40

Edit: In my workbook the city names doesn't contain the numbers after the cities.

 

[Fluff]

If you just want to insert the formula in block of 40 rows try

Sub waimea()
   Dim i As Long
   
   For i = 2 To 1601 Step 40
      Range("I" & i).Resize(40).FormulaR1C1 = "=SUMPRODUCT(r" & i & "c2:r" & i & "c8,rc2:rc8)"
   Next i
End Sub

 

[Waimea]

Hi Fluff,

thank you for your reply. I am inserting the formula in blocks of 40 and then using custom sort to offset the first item in the list to counter the fixed insertion of the formula. I don't understand the R1C1 part.

Could you help me with this formula as well? (In blocks of 40)

=SQRT(SUMSQ($B$2:$H$2))*SQRT(SUMSQ(B2:H2))

 

[Fluff]

Does the code I supplied work for the sumproduct formula?

 

[Waimea]

Hi,

the code you supplied works great!

 

[Fluff]

In that case simply replace the ranges in your new formula the same way as I did for the Sumproduct.

 

[Waimea]

Yes, I will try.

Thank you again!