Solved: Custom sort error

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
If I use custom sort it puts data at top of the list and not somewhere in the middle of the column & then one has to on a hunting mission to find it. Same thing can be done with using conditional format to format entire column to highlight all the values eg 282.03 then color sort puts all at top of list but this is time consuming and would take a lot more computing power and slow things down on big sheets.

c=282.03 <this is a variable amount

Application.Goto Reference:="R47C4"
ActiveWorkbook.Worksheets("zoo").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("zoo").AutoFilter.Sort.SortFields.Add Key:= _
ActiveCell.Range("A1:A50000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:="282.03", DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("zoo").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

but if i replace CustomOrder:="282.03" with customorder:"c" it chucks a tantrum.
Probably a very simple solution but it eludes me..........I have tried various variations without
success.
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,612
Hi spooky1,

First you do not sort on a value of a cell you sort on a header or no header on a whole column
I suggest you take a look at the recorde macro and edit it to do what you really want.
R47C4 should be tranalsted to the addrees it is whihc would be D47.
You don't mention the Excel version but from the macro code it's 2007 newer
I think you should firt order and the set the filter to a specific value but honestly, I don't understand what you want to achieve, sorry
 

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
Thanks for your reply.

why does this work in recorded macro 2007

CustomOrder:="282.03"

but
if c =
282.03

CustomOrder:= C doesn't?


That's all I was asking about
 

spooky1

Thread Starter
Joined
Jan 29, 2010
Messages
126
I'm trying to sort by cell color on another sheet without having the sheet open, visible.

I tried this but cannot get it to work

ActiveWorkbook.Worksheets("zoo").AutoFilter.Sort.SortFields.Sheets("zoo").Range ("d47"), _
Sheets("zoo").Range("d48:q50000"), xlSortOnCellColor, xlAscending, , xlSortNormal, _
SortOnValue.Color = RGB(215, 228, 188)

With Worksheets("zoo").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

This code works with just normal sort:-
Sheets("zoo").Range("d48:q50000").Sort Key1:=Sheets("zoo").Range("d47"), _
Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

some coding would be appreciated as your reply using `autofilter sort' is way over my head and I'm six foot tall!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top