check if a range of strings is sorted?

[blitzo]

I have a list of strings as such

A1: A
A2: B
A3: D
A4: E

How can I check to see if this range is sorted with vba?

I have searched but havent found anything for strings.

I can only think of adding each cell to an array, creating a copy array, sorting the copy array and then comparing the two arrays. I want it to return TRUE, FALSE or 1, 0

Thanks in advance!

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Test()<br>    MsgBox "Range A1:A4 is sorted: " & IsSorted(Range("A1:A4"))<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> IsSorted(rng <SPAN style="color:#00007F">As</SPAN> Range, _<br>                          <SPAN style="color:#00007F">Optional</SPAN> SortOrder <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> = xlAscending, _<br>                          <SPAN style="color:#00007F">Optional</SPAN> Compare <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> = vbTextCompare) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    <SPAN style="color:#007F00">' SortOrder = xlDescending(Z to A) or xlAscending(A to Z default)</SPAN><br>    <br>    <SPAN style="color:#007F00">' Compare = vbBinaryCompare(case sensitive) or</SPAN><br>    <SPAN style="color:#007F00">'           vbTextCompare(default and not case sensitive)</SPAN><br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> v <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, k <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN><br>    <br>    IsSorted = <SPAN style="color:#00007F">True</SPAN><br>    v = rng<br>    <SPAN style="color:#00007F">If</SPAN> SortOrder = xlDescending <SPAN style="color:#00007F">Then</SPAN> j = 1 <SPAN style="color:#00007F">Else</SPAN> k = 1<br>        <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(v, 1) - 1<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">StrComp</SPAN>(v(i + j, 1), v(i + k, 1), Compare) > 0 <SPAN style="color:#00007F">Then</SPAN><br>            IsSorted = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[blitzo]

Holy atomic pile, Batman!

Perfecto. Thanks so much. Much appreciate. Much better than the code i came up with.

 

[AlphaFrog]

Your welcome.

Note: it only works on column data as is. With some small changes it could work on row data or both.