Re: Sorting Excel object?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Ed (ed_millis_at_NO_SPAM.yahoo.com)
Date: 12/07/04 

Date: Tue, 7 Dec 2004 09:33:51 -0700

Chuck, here's a code snippet I use to set a sort. The only other thing I
can see off the top of my head is that you've only got one cell selected,
rather than a range. Read the Sort Method help topic. I think if you only
specify one cell, you only get one Key, which is that column. If you want
more columns, they shold be included in your range to sort. Don't forget to
specify headers, too, if you've got them.

Ed

    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Cells(1)).Select
    Selection.Sort _
        Key1:=Range("AA2"), Order1:=xlAscending _
        , Key2:=Range("X2"), Order2:=xlAscending _
        , Key3:=Range("Y2"), Order3:=xlAscending _
        , Header:=xlYes, OrderCustom:=1, MatchCase:=False _
        , Orientation:=xlTopToBottom

"Chuck" <Chuck@discussions.microsoft.com> wrote in message
news:2264E4A1-C19A-4F14-95E1-CF406EB86F48@microsoft.com...
> Thanks for catching that typo -- I tried changing the Key3 Order to Order
3
> but I got the same error message. I also tried to set a range and sort
the
> range (see below), but again got the same error...
>
> Set rngRange = objExcel.Worksheets(1).Range("A1:e416")
> rngRange.Sort
>
> Any more thoughts?
>
> "Ed" wrote:
>
> > Key 2 and Key 3 are both Order2. Try changing one to Order3 and see if
that
> > helps.
> >
> > Ed
> >
> > "Chuck" <Chuck@discussions.microsoft.com> wrote in message
> > news:2519C882-FE68-4FB1-BBC8-523CC0D363DC@microsoft.com...
> > > Hi. I'm having trouble sorting an Excel spreadsheet that I create as
an
> > > object (see code below).
> > >
> > > The code creates the spreadsheet and populates it from an outside data
> > > source just fine.
> > >
> > > However when I try to sort objExcel.Worksheets(1) (see the code), I
get an
> > > error message saying "Sort method of Range class failed". Thing is,
when
> > I
> > > copy the sorting portion code into the VB editor window for the
> > > objExcel.Worksheet, strip out "objExcel." then the sort works fine.
> > >
> > > Any ideas on how I can sort the Excel object I've created? Thanks...
> > >
> > > Public Sub PopulateWorksheetWithContacts()
> > >
> > > Dim conn As Object
> > > Dim contactSearch As Object
> > > Dim i As Integer
> > > Dim objExcel As New Excel.Application
> > >
> > > Set conn = CreateObject("InterAction.Connection")
> > >
> > > conn.Login
> > >
> > > If conn.IsLoggedIn Then
> > > Set contactSearch = conn.NewContactSearch
> > > contactSearch.FolderId = "Firm Personnel"
> > > contactSearch.Execute
> > > Set contacts = contactSearch.Results
> > > End If
> > >
> > > objExcel.Workbooks.Add
> > >
> > > If Not contacts Is Nothing Then
> > > For i = 1 To contacts.Count
> > > With objExcel.Worksheets(1)
> > > .Cells(i, 1).Value = contacts(i).FirstName
> > > .Cells(i, 2).Value = contacts(i).LastName
> > > .Cells(i, 3).Value = contacts(i).Department
> > > ' .Cells(i, 4).Value = contacts(i).Phones(1).Phone
> > > ' .Cells(i, 5).Value = contacts(i).Eaddresses(1).Address
> > > End With
> > > Next i
> > > Else
> > > MsgBox "There has been a problem populating your Authors list"
> > > End If
> > >
> > > objExcel.Worksheets(1).Range("A1").Sort _
> > > Key1:=Worksheets(1).Columns("A"), Order1:=xlAscending, _
> > > Key2:=Worksheets(1).Columns("b"), Order2:=xlAscending, _
> > > Key3:=Worksheets(1).Columns("c"), Order2:=xlAscending, _
> > > Header:=xlNo, _
> > > OrderCustom:=1, _
> > > MatchCase:=False, _
> > > Orientation:=xlTopToBottom
> > >
> > > End Sub
> > >
> >
> >
> >

Relevant Pages

  • Re: Sorting Excel object?
    ... > Thansk for the suggestion, I modified my code as you suggested but still ... > the error message "Sort method of Range class failed". ... >> Chuck, here's a code snippet I use to set a sort. ... >> specify headers, too, if you've got them. ...
    (microsoft.public.excel.programming)
  • Re: the sort function in lisp (destructive)
    ... As for SORT Robert Maas posted a small hack that would preserve the head ... cons after calling the implementation's SORT in SORT-KEEPING-HEAD-CELL ... then splice the user's head cell back in after sorting. ...
    (comp.lang.lisp)
  • Re: Royal wedding (OT)
    ... How certifiably stupid are the general ... public to adore these sort of people? ... As head of state the queen has no ... If the monarchy did have political power I'd be a republican, ...
    (rec.music.makers.guitar.acoustic)
  • Re: sort + head = weirdness?
    ... >'sort'and then through 'head' and I'm getting really strange results. ... >works fine with 'sort' itself. ... buffer, so there's no attempt to write anything after "head" has exited. ... Check the documentation of the shell (you didn't say what shell you're ...
    (comp.unix.shell)
  • Re: Story opening
    ... I sort of see someone trying to scoot over on a seat that is ... "Flinch" I know exacly what that means. ... Head goes back, eyes blink, hands come up. ...
    (rec.arts.sf.composition)