Hi All,
I have a bubble sort failing when
"a" seems to compare greater then "B"
in a binary text compare. There are notes
in the bubble sort loop below.

Other more 'complicated' data tests well, also
in the notes.

Help is appreciated.
Thanks,
Neal Zimm


Sub zBubble_Sort()
Dim CompareHow As VbCompareMethod

Dim Ix As Long
Dim Jx As Long
Dim Lo As Long, Hi As Long

Dim KeyAy As Variant
Dim sHold As String
Dim sInput As String
Dim sOutput As String

' All binary compares
' KeyAy = Array("a", "A") 'good, result is Aa
' KeyAy = Array("b", "B") 'good, result is Bb
' KeyAy = Array("b", "a", "A") 'good, result = Aab
' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa

CompareHow = vbBinaryCompare
''CompareHow = vbTextCompare

KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
GoSub SortTest

'the above has a problem, see sort. Bummer is that
'the data below works as expected.

KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
GoSub SortTest
For Ix = Lo To Hi
sOutput = sOutput & KeyAy(Ix) & vbCr
Next Ix
MsgBox sOutput
Exit Sub

SortTest:
Lo = LBound(KeyAy)
Hi = UBound(KeyAy)

For Ix = Lo To Hi
sInput = sInput & KeyAy(Ix)
Next Ix

'sort ascending
For Ix = Lo To (Hi - 1)
For Jx = (Ix + 1) To Hi
'The failure is @ Ix=2 "a", it compares greater
' than Jx=4 of "B" WHY ??
If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
sHold = KeyAy(Jx)
KeyAy(Jx) = KeyAy(Ix)
KeyAy(Ix) = sHold
End If
Next Jx
Next Ix


For Ix = Lo To Hi
sOutput = sOutput & KeyAy(Ix)
Next Ix
MsgBox sInput & vbCr & vbCr & sOutput
sInput = ""
sOutput = ""
Return
End Sub

--
Neal Z

That is because a is greater than B in ASCII. IF you want otherwise you will
need to shift to a consistent form, UCase or LCase.

--

HTH

Bob

"Neal Zimm" <(E-Mail Removed)> wrote in message
news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
> Hi All,
> I have a bubble sort failing when
> "a" seems to compare greater then "B"
> in a binary text compare. There are notes
> in the bubble sort loop below.
>
> Other more 'complicated' data tests well, also
> in the notes.
>
> Help is appreciated.
> Thanks,
> Neal Zimm
>
>
> Sub zBubble_Sort()
> Dim CompareHow As VbCompareMethod
>
> Dim Ix As Long
> Dim Jx As Long
> Dim Lo As Long, Hi As Long
>
> Dim KeyAy As Variant
> Dim sHold As String
> Dim sInput As String
> Dim sOutput As String
>
> ' All binary compares
> ' KeyAy = Array("a", "A") 'good, result is Aa
> ' KeyAy = Array("b", "B") 'good, result is Bb
> ' KeyAy = Array("b", "a", "A") 'good, result = Aab
> ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
>
> CompareHow = vbBinaryCompare
> ''CompareHow = vbTextCompare
>
> KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
> GoSub SortTest
>
> 'the above has a problem, see sort. Bummer is that
> 'the data below works as expected.
>
> KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
> GoSub SortTest
> For Ix = Lo To Hi
> sOutput = sOutput & KeyAy(Ix) & vbCr
> Next Ix
> MsgBox sOutput
> Exit Sub
>
> SortTest:
> Lo = LBound(KeyAy)
> Hi = UBound(KeyAy)
>
> For Ix = Lo To Hi
> sInput = sInput & KeyAy(Ix)
> Next Ix
>
> 'sort ascending
> For Ix = Lo To (Hi - 1)
> For Jx = (Ix + 1) To Hi
> 'The failure is @ Ix=2 "a", it compares greater
> ' than Jx=4 of "B" WHY ??
> If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
> sHold = KeyAy(Jx)
> KeyAy(Jx) = KeyAy(Ix)
> KeyAy(Ix) = sHold
> End If
> Next Jx
> Next Ix
>
>
> For Ix = Lo To Hi
> sOutput = sOutput & KeyAy(Ix)
> Next Ix
> MsgBox sInput & vbCr & vbCr & sOutput
> sInput = ""
> sOutput = ""
> Return
> End Sub
>
> --
> Neal Z

In the SortTest sub use
If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then

Note the use of UCASE
But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column. Now do
you see why the sort works the way it does?
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Neal Zimm" <(E-Mail Removed)> wrote in message
news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
> Hi All,
> I have a bubble sort failing when
> "a" seems to compare greater then "B"
> in a binary text compare. There are notes
> in the bubble sort loop below.
>
> Other more 'complicated' data tests well, also
> in the notes.
>
> Help is appreciated.
> Thanks,
> Neal Zimm
>
>
> Sub zBubble_Sort()
> Dim CompareHow As VbCompareMethod
>
> Dim Ix As Long
> Dim Jx As Long
> Dim Lo As Long, Hi As Long
>
> Dim KeyAy As Variant
> Dim sHold As String
> Dim sInput As String
> Dim sOutput As String
>
> ' All binary compares
> ' KeyAy = Array("a", "A") 'good, result is Aa
> ' KeyAy = Array("b", "B") 'good, result is Bb
> ' KeyAy = Array("b", "a", "A") 'good, result = Aab
> ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
>
> CompareHow = vbBinaryCompare
> ''CompareHow = vbTextCompare
>
> KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
> GoSub SortTest
>
> 'the above has a problem, see sort. Bummer is that
> 'the data below works as expected.
>
> KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
> GoSub SortTest
> For Ix = Lo To Hi
> sOutput = sOutput & KeyAy(Ix) & vbCr
> Next Ix
> MsgBox sOutput
> Exit Sub
>
> SortTest:
> Lo = LBound(KeyAy)
> Hi = UBound(KeyAy)
>
> For Ix = Lo To Hi
> sInput = sInput & KeyAy(Ix)
> Next Ix
>
> 'sort ascending
> For Ix = Lo To (Hi - 1)
> For Jx = (Ix + 1) To Hi
> 'The failure is @ Ix=2 "a", it compares greater
> ' than Jx=4 of "B" WHY ??
> If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
> sHold = KeyAy(Jx)
> KeyAy(Jx) = KeyAy(Ix)
> KeyAy(Ix) = sHold
> End If
> Next Jx
> Next Ix
>
>
> For Ix = Lo To Hi
> sOutput = sOutput & KeyAy(Ix)
> Next Ix
> MsgBox sInput & vbCr & vbCr & sOutput
> sInput = ""
> sOutput = ""
> Return
> End Sub
>
> --
> Neal Z

Well Bob,
Crap, not at you, just the situation. If i wanted to use U and L case, I
wouldn't be bothering with the binary compare, the sort works just fine with
the text compare.

So if you want an apha sort in VBA, where case matters, do you have to
build your own ?

Thanks and be well.
--
Neal Z


"Bob Phillips" wrote:

> That is because a is greater than B in ASCII. IF you want otherwise you will
> need to shift to a consistent form, UCase or LCase.
>
> --
>
> HTH
>
> Bob
>
> "Neal Zimm" <(E-Mail Removed)> wrote in message
> news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
> > Hi All,
> > I have a bubble sort failing when
> > "a" seems to compare greater then "B"
> > in a binary text compare. There are notes
> > in the bubble sort loop below.
> >
> > Other more 'complicated' data tests well, also
> > in the notes.
> >
> > Help is appreciated.
> > Thanks,
> > Neal Zimm
> >
> >
> > Sub zBubble_Sort()
> > Dim CompareHow As VbCompareMethod
> >
> > Dim Ix As Long
> > Dim Jx As Long
> > Dim Lo As Long, Hi As Long
> >
> > Dim KeyAy As Variant
> > Dim sHold As String
> > Dim sInput As String
> > Dim sOutput As String
> >
> > ' All binary compares
> > ' KeyAy = Array("a", "A") 'good, result is Aa
> > ' KeyAy = Array("b", "B") 'good, result is Bb
> > ' KeyAy = Array("b", "a", "A") 'good, result = Aab
> > ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
> >
> > CompareHow = vbBinaryCompare
> > ''CompareHow = vbTextCompare
> >
> > KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
> > GoSub SortTest
> >
> > 'the above has a problem, see sort. Bummer is that
> > 'the data below works as expected.
> >
> > KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
> > GoSub SortTest
> > For Ix = Lo To Hi
> > sOutput = sOutput & KeyAy(Ix) & vbCr
> > Next Ix
> > MsgBox sOutput
> > Exit Sub
> >
> > SortTest:
> > Lo = LBound(KeyAy)
> > Hi = UBound(KeyAy)
> >
> > For Ix = Lo To Hi
> > sInput = sInput & KeyAy(Ix)
> > Next Ix
> >
> > 'sort ascending
> > For Ix = Lo To (Hi - 1)
> > For Jx = (Ix + 1) To Hi
> > 'The failure is @ Ix=2 "a", it compares greater
> > ' than Jx=4 of "B" WHY ??
> > If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
> > sHold = KeyAy(Jx)
> > KeyAy(Jx) = KeyAy(Ix)
> > KeyAy(Ix) = sHold
> > End If
> > Next Jx
> > Next Ix
> >
> >
> > For Ix = Lo To Hi
> > sOutput = sOutput & KeyAy(Ix)
> > Next Ix
> > MsgBox sInput & vbCr & vbCr & sOutput
> > sInput = ""
> > sOutput = ""
> > Return
> > End Sub
> >
> > --
> > Neal Z
>
>
> .
>

Cher Bernard,
Merci.
Lcase and Ucase do me no good.

The sort works fine with option compare text, but I wanted "more".

If you need a case sensitive sort, (and I forgot about the ascii
collating sequence) what's the best way without writing to a worksheet and
sorting there?

thanks much.
Neal

--
Neal Z


"Bernard Liengme" wrote:

> In the SortTest sub use
> If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then
>
> Note the use of UCASE
> But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column. Now do
> you see why the sort works the way it does?
> best wishes
> --
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
>
> "Neal Zimm" <(E-Mail Removed)> wrote in message
> news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
> > Hi All,
> > I have a bubble sort failing when
> > "a" seems to compare greater then "B"
> > in a binary text compare. There are notes
> > in the bubble sort loop below.
> >
> > Other more 'complicated' data tests well, also
> > in the notes.
> >
> > Help is appreciated.
> > Thanks,
> > Neal Zimm
> >
> >
> > Sub zBubble_Sort()
> > Dim CompareHow As VbCompareMethod
> >
> > Dim Ix As Long
> > Dim Jx As Long
> > Dim Lo As Long, Hi As Long
> >
> > Dim KeyAy As Variant
> > Dim sHold As String
> > Dim sInput As String
> > Dim sOutput As String
> >
> > ' All binary compares
> > ' KeyAy = Array("a", "A") 'good, result is Aa
> > ' KeyAy = Array("b", "B") 'good, result is Bb
> > ' KeyAy = Array("b", "a", "A") 'good, result = Aab
> > ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
> >
> > CompareHow = vbBinaryCompare
> > ''CompareHow = vbTextCompare
> >
> > KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb ??
> > GoSub SortTest
> >
> > 'the above has a problem, see sort. Bummer is that
> > 'the data below works as expected.
> >
> > KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
> > GoSub SortTest
> > For Ix = Lo To Hi
> > sOutput = sOutput & KeyAy(Ix) & vbCr
> > Next Ix
> > MsgBox sOutput
> > Exit Sub
> >
> > SortTest:
> > Lo = LBound(KeyAy)
> > Hi = UBound(KeyAy)
> >
> > For Ix = Lo To Hi
> > sInput = sInput & KeyAy(Ix)
> > Next Ix
> >
> > 'sort ascending
> > For Ix = Lo To (Hi - 1)
> > For Jx = (Ix + 1) To Hi
> > 'The failure is @ Ix=2 "a", it compares greater
> > ' than Jx=4 of "B" WHY ??
> > If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
> > sHold = KeyAy(Jx)
> > KeyAy(Jx) = KeyAy(Ix)
> > KeyAy(Ix) = sHold
> > End If
> > Next Jx
> > Next Ix
> >
> >
> > For Ix = Lo To Hi
> > sOutput = sOutput & KeyAy(Ix)
> > Next Ix
> > MsgBox sInput & vbCr & vbCr & sOutput
> > sInput = ""
> > sOutput = ""
> > Return
> > End Sub
> >
> > --
> > Neal Z
>
> .
>

So why not use text compare instead of binary.

Regards,
Peter T

"Neal Zimm" <(E-Mail Removed)> wrote in message
news:216D8124-ACAE-4520-BC63-(E-Mail Removed)...
> Cher Bernard,
> Merci.
> Lcase and Ucase do me no good.
>
> The sort works fine with option compare text, but I wanted "more".
>
> If you need a case sensitive sort, (and I forgot about the ascii
> collating sequence) what's the best way without writing to a worksheet and
> sorting there?
>
> thanks much.
> Neal
>
> --
> Neal Z
>
>
> "Bernard Liengme" wrote:
>
>> In the SortTest sub use
>> If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then
>>
>> Note the use of UCASE
>> But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column. Now
>> do
>> you see why the sort works the way it does?
>> best wishes
>> --
>> Bernard Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>>
>> "Neal Zimm" <(E-Mail Removed)> wrote in message
>> news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
>> > Hi All,
>> > I have a bubble sort failing when
>> > "a" seems to compare greater then "B"
>> > in a binary text compare. There are notes
>> > in the bubble sort loop below.
>> >
>> > Other more 'complicated' data tests well, also
>> > in the notes.
>> >
>> > Help is appreciated.
>> > Thanks,
>> > Neal Zimm
>> >
>> >
>> > Sub zBubble_Sort()
>> > Dim CompareHow As VbCompareMethod
>> >
>> > Dim Ix As Long
>> > Dim Jx As Long
>> > Dim Lo As Long, Hi As Long
>> >
>> > Dim KeyAy As Variant
>> > Dim sHold As String
>> > Dim sInput As String
>> > Dim sOutput As String
>> >
>> > ' All binary compares
>> > ' KeyAy = Array("a", "A") 'good, result is Aa
>> > ' KeyAy = Array("b", "B") 'good, result is Bb
>> > ' KeyAy = Array("b", "a", "A") 'good, result = Aab
>> > ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
>> >
>> > CompareHow = vbBinaryCompare
>> > ''CompareHow = vbTextCompare
>> >
>> > KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb
>> > ??
>> > GoSub SortTest
>> >
>> > 'the above has a problem, see sort. Bummer is that
>> > 'the data below works as expected.
>> >
>> > KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
>> > GoSub SortTest
>> > For Ix = Lo To Hi
>> > sOutput = sOutput & KeyAy(Ix) & vbCr
>> > Next Ix
>> > MsgBox sOutput
>> > Exit Sub
>> >
>> > SortTest:
>> > Lo = LBound(KeyAy)
>> > Hi = UBound(KeyAy)
>> >
>> > For Ix = Lo To Hi
>> > sInput = sInput & KeyAy(Ix)
>> > Next Ix
>> >
>> > 'sort ascending
>> > For Ix = Lo To (Hi - 1)
>> > For Jx = (Ix + 1) To Hi
>> > 'The failure is @ Ix=2 "a", it compares greater
>> > ' than Jx=4 of "B" WHY ??
>> > If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
>> > sHold = KeyAy(Jx)
>> > KeyAy(Jx) = KeyAy(Ix)
>> > KeyAy(Ix) = sHold
>> > End If
>> > Next Jx
>> > Next Ix
>> >
>> >
>> > For Ix = Lo To Hi
>> > sOutput = sOutput & KeyAy(Ix)
>> > Next Ix
>> > MsgBox sInput & vbCr & vbCr & sOutput
>> > sInput = ""
>> > sOutput = ""
>> > Return
>> > End Sub
>> >
>> > --
>> > Neal Z
>>
>> .
>>

I have an implementation of QSort on my web site at
http://www.cpearson.com/Excel/QSort.htm . You can download the module
at http://www.cpearson.com/zips/modQSortInPlace.zip . It handles both
case sensitive and case insensitive sorts in either ascending or
descending order. QSort is much faster than a bubble sort. The code
can handle sorting both strings and numerics and it can handle sorting
only a subset of an array.

You would call QSortInPlace with code like

Sub AAA()
Dim Arr(1 To 5) As String
Dim N As Long
Arr(1) = "z"
Arr(2) = "A"
Arr(3) = "a"
Arr(4) = "Z"
Arr(5) = "m"
QSortInPlace InputArray:=Arr, CompareMode:=vbBinaryCompare
For N = 1 To 5
Debug.Print Arr(N)
Next N
End Sub

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Sun, 14 Mar 2010 17:44:01 -0700, Neal Zimm <(E-Mail Removed)>
wrote:

>Cher Bernard,
> Merci.
> Lcase and Ucase do me no good.
>
> The sort works fine with option compare text, but I wanted "more".
>
> If you need a case sensitive sort, (and I forgot about the ascii
>collating sequence) what's the best way without writing to a worksheet and
>sorting there?
>
>thanks much.
>Neal

Peter, I need to build a "complementary" string in order to get the
descending functionality to work. the opposite of a space is the "~"
character. The text compare method did not recogize it. I'll be posting the
working code I've just finished later today. Thanks.

--
Neal Z


"Peter T" wrote:

> So why not use text compare instead of binary.
>
> Regards,
> Peter T
>
> "Neal Zimm" <(E-Mail Removed)> wrote in message
> news:216D8124-ACAE-4520-BC63-(E-Mail Removed)...
> > Cher Bernard,
> > Merci.
> > Lcase and Ucase do me no good.
> >
> > The sort works fine with option compare text, but I wanted "more".
> >
> > If you need a case sensitive sort, (and I forgot about the ascii
> > collating sequence) what's the best way without writing to a worksheet and
> > sorting there?
> >
> > thanks much.
> > Neal
> >
> > --
> > Neal Z
> >
> >
> > "Bernard Liengme" wrote:
> >
> >> In the SortTest sub use
> >> If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then
> >>
> >> Note the use of UCASE
> >> But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column. Now
> >> do
> >> you see why the sort works the way it does?
> >> best wishes
> >> --
> >> Bernard Liengme
> >> Microsoft Excel MVP
> >> http://people.stfx.ca/bliengme
> >>
> >> "Neal Zimm" <(E-Mail Removed)> wrote in message
> >> news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
> >> > Hi All,
> >> > I have a bubble sort failing when
> >> > "a" seems to compare greater then "B"
> >> > in a binary text compare. There are notes
> >> > in the bubble sort loop below.
> >> >
> >> > Other more 'complicated' data tests well, also
> >> > in the notes.
> >> >
> >> > Help is appreciated.
> >> > Thanks,
> >> > Neal Zimm
> >> >
> >> >
> >> > Sub zBubble_Sort()
> >> > Dim CompareHow As VbCompareMethod
> >> >
> >> > Dim Ix As Long
> >> > Dim Jx As Long
> >> > Dim Lo As Long, Hi As Long
> >> >
> >> > Dim KeyAy As Variant
> >> > Dim sHold As String
> >> > Dim sInput As String
> >> > Dim sOutput As String
> >> >
> >> > ' All binary compares
> >> > ' KeyAy = Array("a", "A") 'good, result is Aa
> >> > ' KeyAy = Array("b", "B") 'good, result is Bb
> >> > ' KeyAy = Array("b", "a", "A") 'good, result = Aab
> >> > ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
> >> >
> >> > CompareHow = vbBinaryCompare
> >> > ''CompareHow = vbTextCompare
> >> >
> >> > KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect AaBb
> >> > ??
> >> > GoSub SortTest
> >> >
> >> > 'the above has a problem, see sort. Bummer is that
> >> > 'the data below works as expected.
> >> >
> >> > KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I M")
> >> > GoSub SortTest
> >> > For Ix = Lo To Hi
> >> > sOutput = sOutput & KeyAy(Ix) & vbCr
> >> > Next Ix
> >> > MsgBox sOutput
> >> > Exit Sub
> >> >
> >> > SortTest:
> >> > Lo = LBound(KeyAy)
> >> > Hi = UBound(KeyAy)
> >> >
> >> > For Ix = Lo To Hi
> >> > sInput = sInput & KeyAy(Ix)
> >> > Next Ix
> >> >
> >> > 'sort ascending
> >> > For Ix = Lo To (Hi - 1)
> >> > For Jx = (Ix + 1) To Hi
> >> > 'The failure is @ Ix=2 "a", it compares greater
> >> > ' than Jx=4 of "B" WHY ??
> >> > If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
> >> > sHold = KeyAy(Jx)
> >> > KeyAy(Jx) = KeyAy(Ix)
> >> > KeyAy(Ix) = sHold
> >> > End If
> >> > Next Jx
> >> > Next Ix
> >> >
> >> >
> >> > For Ix = Lo To Hi
> >> > sOutput = sOutput & KeyAy(Ix)
> >> > Next Ix
> >> > MsgBox sInput & vbCr & vbCr & sOutput
> >> > sInput = ""
> >> > sOutput = ""
> >> > Return
> >> > End Sub
> >> >
> >> > --
> >> > Neal Z
> >>
> >> .
> >>
>
>
> .
>

As always, thanks much Chip, I'll be looking at your site later today.

I'll be posting the code I just finished, here in about 10 minutes. I
should know by now to look at your site first, but I wanted the knowledge in
building the sort that I wrote.

I'm kinda proud of it, it sorts an array on any or all of its columns , each
column can be ascending or descending, but you can be sure I'll check yours
out.

Thanks again.
--
Neal Z


"Chip Pearson" wrote:

>
> I have an implementation of QSort on my web site at
> http://www.cpearson.com/Excel/QSort.htm . You can download the module
> at http://www.cpearson.com/zips/modQSortInPlace.zip . It handles both
> case sensitive and case insensitive sorts in either ascending or
> descending order. QSort is much faster than a bubble sort. The code
> can handle sorting both strings and numerics and it can handle sorting
> only a subset of an array.
>
> You would call QSortInPlace with code like
>
> Sub AAA()
> Dim Arr(1 To 5) As String
> Dim N As Long
> Arr(1) = "z"
> Arr(2) = "A"
> Arr(3) = "a"
> Arr(4) = "Z"
> Arr(5) = "m"
> QSortInPlace InputArray:=Arr, CompareMode:=vbBinaryCompare
> For N = 1 To 5
> Debug.Print Arr(N)
> Next N
> End Sub
>
> Cordially,
> Chip Pearson
> Microsoft Most Valuable Professional,
> Excel, 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> On Sun, 14 Mar 2010 17:44:01 -0700, Neal Zimm <(E-Mail Removed)>
> wrote:
>
> >Cher Bernard,
> > Merci.
> > Lcase and Ucase do me no good.
> >
> > The sort works fine with option compare text, but I wanted "more".
> >
> > If you need a case sensitive sort, (and I forgot about the ascii
> >collating sequence) what's the best way without writing to a worksheet and
> >sorting there?
> >
> >thanks much.
> >Neal
> .
>

Not sure what you mean by - the opposite of a space is the "~", however
StrComp with textCompare certainly does recognize the "~", indeed in this
range same as Excel's compare order (which is not entirely the same as
VB's).

Regards,
Peter T


"Neal Zimm" <(E-Mail Removed)> wrote in message
news:5D1149E2-BF66-4CD3-92EB-(E-Mail Removed)...
> Peter, I need to build a "complementary" string in order to get the
> descending functionality to work. the opposite of a space is the "~"
> character. The text compare method did not recogize it. I'll be posting
> the
> working code I've just finished later today. Thanks.
>
> --
> Neal Z
>
>
> "Peter T" wrote:
>
>> So why not use text compare instead of binary.
>>
>> Regards,
>> Peter T
>>
>> "Neal Zimm" <(E-Mail Removed)> wrote in message
>> news:216D8124-ACAE-4520-BC63-(E-Mail Removed)...
>> > Cher Bernard,
>> > Merci.
>> > Lcase and Ucase do me no good.
>> >
>> > The sort works fine with option compare text, but I wanted "more".
>> >
>> > If you need a case sensitive sort, (and I forgot about the ascii
>> > collating sequence) what's the best way without writing to a worksheet
>> > and
>> > sorting there?
>> >
>> > thanks much.
>> > Neal
>> >
>> > --
>> > Neal Z
>> >
>> >
>> > "Bernard Liengme" wrote:
>> >
>> >> In the SortTest sub use
>> >> If StrComp(UCase(KeyAy(Ix)), UCase(KeyAy(Jx)), CompareHow) = 1 Then
>> >>
>> >> Note the use of UCASE
>> >> But A, B, a. B in A1:A4; In B1 enter =CODE(A1);copu down the column.
>> >> Now
>> >> do
>> >> you see why the sort works the way it does?
>> >> best wishes
>> >> --
>> >> Bernard Liengme
>> >> Microsoft Excel MVP
>> >> http://people.stfx.ca/bliengme
>> >>
>> >> "Neal Zimm" <(E-Mail Removed)> wrote in message
>> >> news:46D21FA6-1BA7-482C-88E6-(E-Mail Removed)...
>> >> > Hi All,
>> >> > I have a bubble sort failing when
>> >> > "a" seems to compare greater then "B"
>> >> > in a binary text compare. There are notes
>> >> > in the bubble sort loop below.
>> >> >
>> >> > Other more 'complicated' data tests well, also
>> >> > in the notes.
>> >> >
>> >> > Help is appreciated.
>> >> > Thanks,
>> >> > Neal Zimm
>> >> >
>> >> >
>> >> > Sub zBubble_Sort()
>> >> > Dim CompareHow As VbCompareMethod
>> >> >
>> >> > Dim Ix As Long
>> >> > Dim Jx As Long
>> >> > Dim Lo As Long, Hi As Long
>> >> >
>> >> > Dim KeyAy As Variant
>> >> > Dim sHold As String
>> >> > Dim sInput As String
>> >> > Dim sOutput As String
>> >> >
>> >> > ' All binary compares
>> >> > ' KeyAy = Array("a", "A") 'good, result is Aa
>> >> > ' KeyAy = Array("b", "B") 'good, result is Bb
>> >> > ' KeyAy = Array("b", "a", "A") 'good, result = Aab
>> >> > ' KeyAy = Array("B", "a", "A") 'NOPE, result = ABa
>> >> >
>> >> > CompareHow = vbBinaryCompare
>> >> > ''CompareHow = vbTextCompare
>> >> >
>> >> > KeyAy = Array("b", "a", "A", "B") 'crap, result = ABab expect
>> >> > AaBb
>> >> > ??
>> >> > GoSub SortTest
>> >> >
>> >> > 'the above has a problem, see sort. Bummer is that
>> >> > 'the data below works as expected.
>> >> >
>> >> > KeyAy = Array("z i m", "z i M", "z I m", "z I M", "Z I m", "Z I
>> >> > M")
>> >> > GoSub SortTest
>> >> > For Ix = Lo To Hi
>> >> > sOutput = sOutput & KeyAy(Ix) & vbCr
>> >> > Next Ix
>> >> > MsgBox sOutput
>> >> > Exit Sub
>> >> >
>> >> > SortTest:
>> >> > Lo = LBound(KeyAy)
>> >> > Hi = UBound(KeyAy)
>> >> >
>> >> > For Ix = Lo To Hi
>> >> > sInput = sInput & KeyAy(Ix)
>> >> > Next Ix
>> >> >
>> >> > 'sort ascending
>> >> > For Ix = Lo To (Hi - 1)
>> >> > For Jx = (Ix + 1) To Hi
>> >> > 'The failure is @ Ix=2 "a", it compares greater
>> >> > ' than Jx=4 of "B" WHY ??
>> >> > If StrComp(KeyAy(Ix), KeyAy(Jx), CompareHow) = 1 Then
>> >> > sHold = KeyAy(Jx)
>> >> > KeyAy(Jx) = KeyAy(Ix)
>> >> > KeyAy(Ix) = sHold
>> >> > End If
>> >> > Next Jx
>> >> > Next Ix
>> >> >
>> >> >
>> >> > For Ix = Lo To Hi
>> >> > sOutput = sOutput & KeyAy(Ix)
>> >> > Next Ix
>> >> > MsgBox sInput & vbCr & vbCr & sOutput
>> >> > sInput = ""
>> >> > sOutput = ""
>> >> > Return
>> >> > End Sub
>> >> >
>> >> > --
>> >> > Neal Z
>> >>
>> >> .
>> >>
>>
>>
>> .
>>