+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 code to sort certain cells depending on current active cell

Hybrid View

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    48067
    MS-Off Ver
    Excel 2007
    Posts
    12

    Excel 2007 code to sort certain cells depending on current active cell

    Hey All, I've been scouring around the forums and haven't been able to come up with a solid answer to this: I'm trying to sort a section of data by a date column. The section to be sorted depends on where the user is entering information. For example the user would press a button to open a form and specify what month they are entering data for. The script finds that month, adds a new line of data, and then I want it to automatically sort the data for February by date.

    here's my code so far thats giving me problems, the Set Top and Set Bottom parts is where it hangs. Is there a better way to do this? (the Sorting part I got by recording a macro and changing the values to the variables i set)
    Private Sub cbAdd_Click()
    
    Dim Top
    Dim Bottom
    
    ActiveCell.Offset(0, -1).Select
    
    Set Top = Range(ActiveCell)	'selects the top of the current range to be sorted
    Set Bottom = Range(ActiveCell.End(xlDown)) 'selects the bottom cell of the range to be sorted
    
        Range(Top, Bottom).Select
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Range(Top) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Daily 2011").Sort
            .SetRange Range(Top, Bottom)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End If
    End Sub
    Last edited by mpalm; 05-26-2011 at 10:28 PM.

  2. #2
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Excel 2007 code to sort certain cells depending on current active cell

    mpalm,

    Quote Originally Posted by mpalm View Post
    the Set Top and Set Bottom parts is where it hangs.
    Set Top = Range(ActiveCell)
    Set Bottom = Range(ActiveCell.End(xlDown))
    ...
        Range(Top, Bottom).Select
    ...
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Range(Top) _
    ...
            .SetRange Range(Top, Bottom)
    In the above quoted code is where your problems are. The issue is that Range(ActiveCell) is invalid because the Range() is unnecessary. Leave that part out, and the code will look like:
    Set Top = ActiveCell
    Set Bottom = ActiveCell.End(xlDown)
    ...
        Range(Top.Address, Bottom.Address).Select
    ...
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Top _
    ...
            .SetRange Range(Top.Address, Bottom.Address)


    Make those changes and give it a try
    ~tigeravatar

  3. #3
    Registered User
    Join Date
    05-26-2011
    Location
    48067
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Excel 2007 code to sort certain cells depending on current active cell

    Thanks that worked for that section but now I get this error: "Method 'Range' of object '_global' failed"
    on this line:

    ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Range(Top) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Excel 2007 code to sort certain cells depending on current active cell

    mpalm,

    From my above post:
        ActiveWorkbook.Worksheets("Daily 2011").Sort.SortFields.Add Key:=Top _
    You need to remove the Range() from the Key:=Range(Top)

    ~tigeravatar

  5. #5
    Registered User
    Join Date
    05-26-2011
    Location
    48067
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Excel 2007 code to sort certain cells depending on current active cell

    Ahhh balls I missed that, alright thanks i'll give that a try.
    I appreciate your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1