Works on numbers but not on TEXT
 

Hi All.........
This formula seems to work on TEXT as well as numbers.
=IF(D10=D9,1,0)

But, when I try to use the code below, it will not work on TEXT...yet it
works fine on numbers.......can it be fixed to work on TEXT as well?

Sub WhichSort()
Dim RNG1
Dim lastcolumn As Long, c As Long
Dim lastrow As Long, r As Long
lastcolumn = Cells(Columns.Count).End(xlToLeft).Column
For c = lastcolumn To 1 Step -1
RNG1 = c
lastrow = Cells(Rows.Count, RNG1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, RNG1) = Cells(r, RNG1).Offset(-1, 0) Then
Cells(r, RNG1).Select
Else
GoTo 100
End If
Next r
ActiveCell.EntireColumn.Interior.ColorIndex = 4
100
Next c
MsgBox "Data sorted on the COLORED column, ASCENDING", vbOKOnly
Columns("A:M").Select
Selection.Interior.ColorIndex = xlNone
Range("F1").Select
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3

Works on numbers but not on TEXT
 

The sort in Excel is case insensitive. You comparison isn't.

This will work with text, but not numbers:

Sub WhichSort()
Dim RNG1
Dim lastcolumn As Long, c As Long
Dim lastrow As Long, r As Long
lastcolumn = Cells(Columns.Count).End(xlToLeft).Column
Cells.Interior.ColorIndex = xlNone
Debug.Print lastcolumn
For c = lastcolumn To 1 Step -1
RNG1 = c
lastrow = Cells(Rows.Count, RNG1).End(xlUp).Row
For r = lastrow To 2 Step -1
If StrComp(Cells(r, RNG1), _
Cells(r - 1, RNG1), vbTextCompare) = 0 Then
Cells(r, RNG1).Select
Else
GoTo 100
End If
Next r
ActiveCell.EntireColumn.Interior.ColorIndex = 4
100
Next c
MsgBox "Data sorted on the COLORED column, ASCENDING", vbOKOnly
Columns("A:M").Select
Selection.Interior.ColorIndex = xlNone
Range("F1").Select
End Sub

--
Regards,
Tom Ogivly


"CLR" wrote:

Hi All.........
This formula seems to work on TEXT as well as numbers.
=IF(D10=D9,1,0)

But, when I try to use the code below, it will not work on TEXT...yet it
works fine on numbers.......can it be fixed to work on TEXT as well?

Sub WhichSort()
Dim RNG1
Dim lastcolumn As Long, c As Long
Dim lastrow As Long, r As Long
lastcolumn = Cells(Columns.Count).End(xlToLeft).Column
For c = lastcolumn To 1 Step -1
RNG1 = c
lastrow = Cells(Rows.Count, RNG1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, RNG1) = Cells(r, RNG1).Offset(-1, 0) Then
Cells(r, RNG1).Select
Else
GoTo 100
End If
Next r
ActiveCell.EntireColumn.Interior.ColorIndex = 4
100
Next c
MsgBox "Data sorted on the COLORED column, ASCENDING", vbOKOnly
Columns("A:M").Select
Selection.Interior.ColorIndex = xlNone
Range("F1").Select
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3

Works on numbers but not on TEXT
 

That did the trick Tom, thanks very much......I can just run both of them in
series and get both effects.

Vaya con Dios,
Chuck, CABGx3



"Tom Ogilvy" wrote:

The sort in Excel is case insensitive. You comparison isn't.

This will work with text, but not numbers:

Sub WhichSort()
Dim RNG1
Dim lastcolumn As Long, c As Long
Dim lastrow As Long, r As Long
lastcolumn = Cells(Columns.Count).End(xlToLeft).Column
Cells.Interior.ColorIndex = xlNone
Debug.Print lastcolumn
For c = lastcolumn To 1 Step -1
RNG1 = c
lastrow = Cells(Rows.Count, RNG1).End(xlUp).Row
For r = lastrow To 2 Step -1
If StrComp(Cells(r, RNG1), _
Cells(r - 1, RNG1), vbTextCompare) = 0 Then
Cells(r, RNG1).Select
Else
GoTo 100
End If
Next r
ActiveCell.EntireColumn.Interior.ColorIndex = 4
100
Next c
MsgBox "Data sorted on the COLORED column, ASCENDING", vbOKOnly
Columns("A:M").Select
Selection.Interior.ColorIndex = xlNone
Range("F1").Select
End Sub

--
Regards,
Tom Ogivly


"CLR" wrote:

Hi All.........
This formula seems to work on TEXT as well as numbers.
=IF(D10=D9,1,0)

But, when I try to use the code below, it will not work on TEXT...yet it
works fine on numbers.......can it be fixed to work on TEXT as well?

Sub WhichSort()
Dim RNG1
Dim lastcolumn As Long, c As Long
Dim lastrow As Long, r As Long
lastcolumn = Cells(Columns.Count).End(xlToLeft).Column
For c = lastcolumn To 1 Step -1
RNG1 = c
lastrow = Cells(Rows.Count, RNG1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Cells(r, RNG1) = Cells(r, RNG1).Offset(-1, 0) Then
Cells(r, RNG1).Select
Else
GoTo 100
End If
Next r
ActiveCell.EntireColumn.Interior.ColorIndex = 4
100
Next c
MsgBox "Data sorted on the COLORED column, ASCENDING", vbOKOnly
Columns("A:M").Select
Selection.Interior.ColorIndex = xlNone
Range("F1").Select
End Sub

TIA
Vaya con Dios,
Chuck, CABGx3