Sorting Macro Issue

[haleybusche]

I recorded a macro to sort my data in three ways: color, number, and letter. The color column is created using a function GetColor() which I wrote.

This assigns a number to the color in the cell and then I can sort it based on the number. The other two sorts are just standard.

When I run the macro, it doesn't sort correctly. And it doesn't seem to sort the same each time.


Sub Sort() 'Sorting by Color(#) ("AB"), letter ("B), TAT#("A")

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range( _
"AB3:AB189"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B3:B189") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A3:A189") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A2:AB189")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

My data is dynamic, so the number of rows will change. The number of columns will not. I use A-AB.

I need to sort by AB, A, and then B. Not just the column itself but the associated row as well.

I am not sure, where I am off, but this doesn't seem to be working. Also if there is a more efficient way of doing this I am all ears.

 

[Weaver]

How are you using the 'getcolour()' function? I'm wondering if it's using absolute references, which might mean that after a sort, it's not looking at the right row. Double click a cell with the formula in, after a sort, to make sure it's still referring to the cell you'd expect.

 

[haleybusche]

It seems to still be referencing the correct cells. Below is the Getcolor() function I am using.

Function GetColor(rngIndex As Range, rngSource As Range) As Long
Dim lngColor As Long
Dim J As Integer

Application.Volatile
lngColor = rngSource.Interior.ColorIndex

GetColor = 99 'Set to default color
For J = 1 To rngIndex.Count
If rngIndex(J).Interior.ColorIndex = lngColor Then
GetColor = J
End If
Next J
End Function

 

[Weaver]

and the colours aren't the result of conditional formatting...?

 

[haleybusche]

The colors are are a result of another macro....if the date in one cell is after today's date then the cell is a particular color, if the date in that cell is before today's date then it is a particular color, etc....

I can post that code, but there is a lot of it.

 

[Weaver]

Try changing the ranges in the key statements to just the header cell. So "AB3:AB189" becomes "AB2". This is a bit of a stab in the dark, I have to admit.

You're forcing headers, so I think it'll work.

I have to admit I still use the xl 2003 syntax for vb sorting - much simpler.

 

[haleybusche]

Still doesn't seem to be working. It is obviously an issue with the color sort, the other two seem to be working fine.

I get one random row in the wrong spot every time.

What is the 2003 way of sorting? Would it be worth trying?