VBA Code - Custom Sort Data based on Range

[tlc53]

Hi,

I have data headings on cells A50:K50. The length of the data underneath can not be determined, as it'll change all the time.

I would like to sort the data underneath by account number, which is located in column K (header K50). The order I would like it sorted by is listed under K43:K48.

I've had a look but can't find information on a VBA code to sort like this.

Can anyone help with this please?

Thank you.

 

[tlc53]

Appreciate you trying. Thank you!
I'll play around with it today but currently I'm stumped. It seems to have a mind of its own

 

[lrobbo314]

Here's a different approach. Seems to be working on my end and you don't have to worry about forcing anything to be text with this method.

Sub CustomSortII()
Application.ScreenUpdating = False
Dim r As Range
Dim cust As Range


Set r = Range("A50:K" & Range("A" & Rows.Count).End(xlUp).Row)
Set cust = Range("K43:K48")


cust.Offset(, 1).Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6))
r.Columns(11).Offset(1, 1).Resize(r.Rows.Count - 1).FormulaR1C1 = "=VLOOKUP(RC[-1],R43C11:R48C12,2,0)"
r.Value = r.Value
Set r = r.Resize(r.Rows.Count, r.Columns.Count + 1)
r.Sort Key1:=[L50], Order1:=xlAscending, Header:=xlYes
r.Columns(12).ClearContents
cust.Offset(, 1).Value = vbNullString
Application.ScreenUpdating = True
End Sub

 

[tlc53]

I'm beginning to think it's me! I don't know why but it's not working. It's not throwing back any errors either.
My data references have changed so that the custom list is located K20:K25 and my headers for the data sort are located on A52:K52.

Sub CustomSortII()
Application.ScreenUpdating = False
Dim r As Range
Dim cust As Range




Set r = Range("A52:K" & Range("A" & Rows.Count).End(xlUp).Row)
Set cust = Range("K20:K25")




cust.Offset(, 1).Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6))
r.Columns(11).Offset(1, 1).Resize(r.Rows.Count - 1).FormulaR1C1 = "=VLOOKUP(RC[-1],R43C11:R48C12,2,0)"
r.Value = r.Value
Set r = r.Resize(r.Rows.Count, r.Columns.Count + 1)
r.Sort Key1:=[L52], Order1:=xlAscending, Header:=xlYes
r.Columns(12).ClearContents
cust.Offset(, 1).Value = vbNullString
Application.ScreenUpdating = True
End Sub

Have I amended the code correctly? I wasn't sure about the Key1: being L50 or L52, so I tried both. It didn't seem to make a difference.

 

[lrobbo314]

The formulaR1C1 needed to be adjusted.

Sub CustomSortII()
Application.ScreenUpdating = False
Dim r As Range
Dim cust As Range


Set r = Range("A52:K" & Range("A" & Rows.Count).End(xlUp).Row)
Set cust = Range("K20:K25")


cust.Offset(, 1).Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6))
r.Columns(11).Offset(1, 1).Resize(r.Rows.Count - 1).FormulaR1C1 = [COLOR=#0000ff][B]"=VLOOKUP(RC[-1],R20C11:R25C12,2,0)"[/B][/COLOR]
r.Value = r.Value
Set r = r.Resize(r.Rows.Count, r.Columns.Count + 1)
r.Sort Key1:=[L52], Order1:=xlAscending, Header:=xlYes
r.Columns(12).ClearContents
cust.Offset(, 1).Value = vbNullString
Application.ScreenUpdating = True
End Sub

 

[tlc53]

Yippee!! It works! Thank you

 

[lrobbo314]

───────────────────░█▓▓▓█░▇▆▅▄▃▂
──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂

 

[tlc53]

The formulaR1C1 needed to be adjusted.

Hi again,
Small error occurring with this code. It's returning Run-time Error 1004 and highlighting this row

r.Columns(11).Offset(1, 1).Resize(r.Rows.Count - 1).FormulaR1C1 = "=VLOOKUP(RC[-1],R20C11:R25C12,2,0)"

I believe this error is occurring because on this occasion, there is no data for this client. So basically nothing to sort, so it doesn't need to run.
Is it possible to amend the code so it recognises if there's no data to sort and therefore stops running?
Thank you!

 

[tlc53]

Hi again,
Small error occurring with this code. It's returning Run-time Error 1004

Worked it out thank you. This addtional line did the trick

If Range("A53") = 0 Then Exit Sub