Sorting Macro Issue

haleybusche

New Member
Joined
Mar 7, 2011
Messages
13
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.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Mar 7, 2011
Messages
13
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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
and the colours aren't the result of conditional formatting...?
 

haleybusche

New Member
Joined
Mar 7, 2011
Messages
13

ADVERTISEMENT

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

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
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

New Member
Joined
Mar 7, 2011
Messages
13
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?
 

Forum statistics

Threads
1,141,734
Messages
5,708,168
Members
421,549
Latest member
Dtcfire

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top