Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: VBA Code - Custom Sort Data based on Range

  1. #11
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    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

  2. #12
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    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.

    Code:
    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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #13
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    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.

    Code:
    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.

  4. #14
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    The formulaR1C1 needed to be adjusted.

    Code:
    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],R20C11:R25C12,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
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #15
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Yippee!! It works! Thank you

  6. #16
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    ───────────────────░█▓▓▓█░▇▆▅▄▃▂
    ──────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─────────────────░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────────░░░───░█▓▓▓▓▓▓█░▇▆▅▄▃▂ ...
    ─────────░███░──░█▓▓▓▓▓█░▇▆▅▄▃▂
    ───────░██░░░██░█▓▓▓▓▓█░▇▆▅▄▃▂
    ──────░█░░█░░░░██▓▓▓▓▓█░▇▆▅▄▃▂
    ────░██░░█░░░░░░█▓▓▓▓█░▇▆▅▄▃▂
    ───░█░░░█░░░░░░░██▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░░░░█░░░░░░░░█▓▓▓█░▇▆▅▄▃▂
    ──░█░░█░░░█░░░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░█░░░░██░░░░░░█▓▓█░▇▆▅▄▃▂
    ─░█░░░░█░░░░░██░░░█▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░█░░░░░░███▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░█░░░██░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░█░░░░█████▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░░░░░█░░░░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ░█░█░░░░██░░░░█▓▓▓▓▓█░▇▆▅▄▃▂
    ─░█░█░░░░░████▓▓▓▓██░▇▆▅▄▃▂
    ─░█░░█░░░░░░░█▓▓██▓█░▇▆▅▄▃▂
    ──░█░░██░░░██▓▓█▓▓▓█░▇▆▅▄▃▂
    ───░██░░███▓▓██▓█▓▓█░▇▆▅▄▃▂
    ────░██▓▓▓███▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓█▓▓▓█░▇▆▅▄▃▂
    ──────░█▓▓▓▓▓▓▓▓▓▓▓▓▓█░▇▆▅▄▃▂
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  7. #17
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Quote Originally Posted by lrobbo314 View Post
    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
    Code:
    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!

  8. #18
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    308
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Quote Originally Posted by tlc53 View Post
    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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •