VBA: Check if a column is in alphabetical order

[Legacy 143009]

Hi Guys!
I need a script which checks the alphabetical order of a ray.
For Ex.

If (A2:A4) in alphabetical order Then
.
.
If not
.
.
End If

Thanks A lot!

 

[Gary's Student]

Are the values letters or words?

 

[Legacy 143009]

Words but random words. I don't have a set for them. They are names and can be something like 168 different human names.

 

[Gary's Student]

How about:

Sub AlphaCompare()
If [A1] < [A2] And [A2] < [A3] And [A3] < [A4] Then
    MsgBox "items in alphabetic order"
Else
    MsgBox "items not in alphabetic order"
End If
End Sub

 

[Legacy 143009]

Hah haa!! How I didn't see this! Of course it will work! thanks!!!

Edit: But if I want to check from A2, I can start the comparison from A2, ain't it?

 

[Gary's Student]

Yes.......you can add or remove terms as required.


B.T.W.

If you have many items in a column, we would code it using a loop rather than a chain of Ands

 

[Legacy 143009]

No, just 3 cells.

 

[Rick Rothstein]

Hah haa!! How I didn't see this! Of course it will work! thanks!!!

Edit: But if I want to check from A2, I can start the comparison from A2, ain't it?

If you have more than 3 cells to compare in Column A, here is general code to do so...

LastRow = Cells(Rows.Count,"A").end(xlup).row 
If Evaluate("SUMPRODUCT(--(A2:A" & LastRow - 1 & ">A3:A" & LastRow & "))") then
  MsgBox "NOT in alphabetical order"
Else
  MsgBox "In alphabetical order"
End If

 

[Gary's Student]

Very nice Rick..............Given that words as well as numbers can be compared with ">" and "<", I wonder how difficult it would be to create a VBA MAX() and MIN() function for words??

 

[mikerickson]

This function will return 1 if the range argument is sorted ascending, -1 if descending, 0 otherwise.
Note: 1, 2, 2, 3, 4, is not considered to be "sorted".

Function OrderedBy(someRange as Range) as Long
    Dim i As Long

    For i = 1 to someRange.Cells.Count
        If Application.CountIf(someRange, "<" & CStr(someRange.Item(i).Value) = (i - 1) Then
            OrderedBy = OrderedBy + 1
        End If
        If Application.CountIf(someRange, ">" & CStr(someRange.Item(i).Value = (i - 1) Then
            OrderedBy = OrderedBy - 1
        End If
    Next i

    If Abs(OrderedBy) = SomeRange.Cells.Count Then
        OrderedBy = Sgn(OrderedBy)
    Else
        OrderedBy = 0
    End If
End Function