Consulting

Results 1 to 4 of 4

Thread: Sort using the active cell value

  1. #1
    VBAX Regular
    Joined
    Sep 2016
    Posts
    7
    Location

    Sort using the active cell value

    Hello everyone.

    Main idea: create a table where people can sort information selecting a predefined word (from many) and pressing a button. With that, I tried to create a code where my idea was to make excel identify the activecell (predefined word), copy and paste the active selection to a random cell, put that value in a variable and use the variable's value to sort stuff. I've tried to adapt the code created by the Record Macro tool, but to no avail.

    Sub Sort_Result1()
        
        Dim r As Range
        Dim c
    
        Set r = ActiveCell
        ActiveSheet.r.Copy <--- First error (of many, so it seems...)
        ActiveSheet.Paste("AJ12")
        
        Set c = Range("AJ12")
                    
        Application.AddCustomList c
        ActiveSheet.Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=Range("F5:F25"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= c _
            , DataOption:=xlSortNormal
        
        With ActiveSheet.Sort
            .SetRange Range("B4:F25")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        
        End With
    
    End Sub
    Any inputs, thoughts?

    Thanks, and sorry if this question is way too stupid.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,000
    Location
    selecting a predefined word (from many)
    Would the predefined word be the column header to sort by?
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Sep 2016
    Posts
    7
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Would the predefined word be the column header to sort by?
    Hey!

    No. I made a column of "key words", that I called "predefined words to sort", located from L5:L44, in a total of 40 possibilities (two examples of predefined words: Ecology and Environmental Science).

  4. #4
    VBAX Regular
    Joined
    Sep 2016
    Posts
    7
    Location
    After almost 2 days trying to make it work, I finally made it. Yay

    I'm posting here the rewriten code, for future reference if anyone needs:

    Sub Sort()
        Dim ws As Worksheet
        Dim act
        Dim src
        
        
        Set ws = ActiveWorkbook.ActiveSheet
        Set act = ActiveCell
        src = act
        
        ws.Range("AJ12").Value = src
           
        ws.AutoFilter.Sort.SortFields.Clear
        ws.AutoFilter.Sort.SortFields.Add Key:=Range( _
        "F5:f86"), SortOn:=xlSorteOnValues, Order:=xlAscending, _
        CustomOrder:=CVar(src), DataOption:=xlSortNormal
                
        With ws.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End Sub

Posting Permissions

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