Comparing Values for Sorting

  • Thread starter Thread starter Suh, Suk Ho
  • Start date Start date
S

Suh, Suk Ho

Dear Colleagues;

How do I sort an array containing the following string values?

I tried using Strcmp function and < > comparison to compare each elements,
but the results don't look as I expected. for example if I compare 030814-01
with 030811-02, Strcomp renders a reverse result. But when I paste this
value into a sheet the Sort works ok. So, I'm lost.

Any guidances will be most appreciated.


from this

030814-01
030409-01-R1
030821-01
030731-01
030821-02
030613-01
030613-01R1
030821-02R1
030811-01R1
030823-01
030811-02

to this

030409-01-R1
030613-01
030613-01R1
030731-01
030811-01R1
030811-02
030814-01
030821-01
030821-02
030821-02R1
030823-01

Suk Ho
 
I pasted in your data:

030613-01 ' in cell A1
030613-01R1 ' in cell A2
030731-01
030811-01R1
030811-02
030814-01
030821-01
030821-02
030821-02R1
030823-01


and in the cell next to each I put in a UDF

Function CompStr(rng As Range, rng1 As Range)
CompStr = StrComp(rng, rng1, vbTextCompare)
End Function

starting in B1 so that the formula in B1 was

=Compstr(A1,A2)

In each case, the formula returned -1 indicating that the value in that row
was less than the value in the next row - so that string compare was/is
returning the same evaluation as the Sort command.

It is unclear how you are using strcomp, but I would suggest your code is
misinterpreting what is returned.
 
Back
Top