Hi Jim.
Thank you.
I'm going to read through, and get a better feel for your macro, and go from
there for now. I'm also going to look up array sorting. I just tried on this
newsgroup and came back nil.
I did a quick search on google, and have yet to review the responses I got.
I will definitely be back if I get stuck, or to let you know that I'm good.
Thanks again for your time.
Best.
"Jim Cone" wrote:
> You can do a search on array sorting and find quite a few solutions.
> There some array sorting routines that are almost incomprehensible,
> blinding fast and very long. However, the Excel worksheet sort
> is as fast as most and is built-in to Excel. So I am sticking with my
> original post that could be implemented something like this...
> '--
> Sub Sludge()
> 'Jim Cone - Portland, Oregon USA - Jan 2009
> Dim rng As Range
> Dim arrTitles() As Variant
> ReDim arrTitles(1 To 50, 1 To 1)
>
> 'fill array - line below used for testing
> 'arrTitles() = Range("G1:G50").Value
>
> Set rng = Range(Cells(1, 2), Cells(UBound(arrTitles(), 1), 2))
> rng.Value = arrTitles()
> With rng.Offset(0, 1)
> .Formula = "=Len(" & rng(1).Address(False, False) & ")"
> .Value = .Value
> End With
>
> 'now sort as per previous post
> rng.Resize(, 2).Sort key1:=rng, order1:=xlAscending, _
> dataoption1:=xlSortTextAsNumbers
> rng.Resize(, 2).Sort key1:=rng.Offset(0, 1), order1:=xlAscending
> arrTitles() = rng.Value
>
> 'do something with sorted array
>
> End Sub
> '--
> Note: If you have already named the worksheets and they are in
> the correct order, then you could just pick off the worksheet names
> and place them in the array.
> '--
> Jim Cone
> Portland, Oregon USA
> (trial version of "Special Sort" Excel add-in available upon request - 2 dozen ways to sort)
> (remove xxx from email address)
>
>
>
> "SteveDB1"
> <(E-Mail Removed)>
> wrote in message
> Hi Jim.
> Thanks for the response.
> What we're doing is extracting the numeric strings from cells on a worksheet.
> We then want to order the numeric strings in an array, and that array then
> "sends" the strings to the built-in-document-properties comment box.
>
> You know, let me back up here a ways and start from the beginning.
> There are 3 macros that are linked together for the purpose of extracting
> the strings, storing them in an array, naming worksheets for each file, and
> then placing the numeric strings in the built-in-document-properties comments
> box.
>
> Everything works perfectly-- we've been working out the kinks for a while
> now-- and the last thing that's bothering us is the disordered numeric
> strings.
> Hency my post.
> If you want to see our code, let me know-- it's a long one, which is why I
> didn't post it. I figured it'd overwhelm anyone who looked at it.
> Thank you.
>
>
> "Jim Cone" wrote:
>
> >
> > Add a column that contains the length of the cell text.
> > Sort both columns by the text column (using xlTextAsNumbers).
> > Sort both columns by the length column.
> > Clear or delete the length column.
> > --
> > Jim Cone
> > Portland, Oregon USA
> > (trial version of "Special Sort" add-in available upon request - 2 dozen ways to sort)
> >
> >
> >
> > "SteveDB1"
> > <(E-Mail Removed)>
> > wrote in message
> > Morning all.
> > A colleague and I are trying to make a macro that will sort a series of
> > numbers in ascending order. However, we've found that what happens is that it
> > does not take into account the number of characters are in each number.
> > I.e., here is a sample
> > 01234, 3245, v01456, 54678, 3765, 4568, 11234, 7534
> >
> > what we've found is that the numbers are arranged as follows.
> > 11234, 3234, 3765, 4568, 54678, 7534, 01234, v01456
> >
> > What we'd like is:
> > 3234, 3765, 4568, 7534, 01234, 11234, 54678, v01456
> > i.e., in an ascending, qty of digits, numbering.
> >
> > How would we accomplish doing this?
> >
> > If you have any questions to help better clarify, please ask.
> >
> > Our present code set is placing these numbers in the builtin document
> > properties comment block. It's working fine except for this last part or
> > ordering the numbers the way we need them ordered.
> > Thank you in advance.
> > Best.
> >
> >
>
Similar Threads
