1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Custom sort error

Discussion in 'Business Applications' started by spooky1, Feb 10, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. spooky1

    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.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    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
     
  3. spooky1

    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
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    I think because you use an AutoFilter Sort
     
  5. spooky1

    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!
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    282.03 is a string not a value
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088935

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice