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

**mpalm** · 05-26-2011, 04:20 PM

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

**tigeravatar** · 05-26-2011, 04:34 PM

mpalm,

Originally Posted by mpalm

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

**mpalm** · 05-26-2011, 04:56 PM

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

**tigeravatar** · 05-26-2011, 04:59 PM

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

**mpalm** · 05-26-2011, 05:00 PM

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

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions