Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Sorting with null values

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am working on a truckload bid for my firm and the client wants the truckload bids sorted by the lowest price. That is easy. However, he knows not every carrier is going to bid and he doesn't want the carriers who do not bid showing up first just because their bid is $0.00 and the actual lowest is $1.05.

    Now, there are over 100 hundred separate lanes that I need to sort by lowest price so I am using a macro to do that. However, I still need to set in the VBA code (am a beginner at VBA so please go easy on teh jargon) a way for the null values to go to the back even though, technically the null values are the lowest.

    I hope I have made this clear and any help I can get would be greatly appreciated.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-02 10:35, jayc28 wrote:
    I am working on a truckload bid for my firm and the client wants the truckload bids sorted by the lowest price. That is easy. However, he knows not every carrier is going to bid and he doesn't want the carriers who do not bid showing up first just because their bid is $0.00 and the actual lowest is $1.05.

    Now, there are over 100 hundred separate lanes that I need to sort by lowest price so I am using a macro to do that. However, I still need to set in the VBA code (am a beginner at VBA so please go easy on teh jargon) a way for the null values to go to the back even though, technically the null values are the lowest.

    I hope I have made this clear and any help I can get would be greatly appreciated.
    If you had a small spreadsheet and weren't
    using VBA, you could use something like:
    =IF(COUNTIF($C$2:$C$16,">0")0,$C$2:$C$16),E2))

    Where C2:C16 was the range to be sorted and
    E2:E16 = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}.
    This is an array formula (entered with control-shift-enter), so it's (a) ugly
    and (b) apt to slow big spreadsheets down.


    But there is an easy way around this.
    If your data is in C2:C16, insert a column
    D, with D2's formula =IF(C2=0,1,0).
    Copy this down as required.

    Then the following VBA code will put the
    nulls at the bottom:
    Range("C2:D16").Select
    Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Key2:=Range("C2") _
    , Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
    False, Orientation:=xlTopToBottom

    This should do what you need.
    "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •