Results 1 to 8 of 8

Thread: Sorting problem

  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Question Unanswered: Sorting problem

    Hi all,
    I have alist of records containing the name and income number, as follows:

    A Name B Income
    ----- -------
    1 John 9000
    2 Mary 8500
    3 Smith 10000


    I want to sort them in the same sheet but not in the same columns, i want them to be displayed in columns E and F, and when i change the value of field B2 to become 12000, i want it to automatically sorted and refreshed in columns E and F.

    thanx

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    If I understand correctly, you want Columns A and B sorted, then Columns E and F to reflect that new sorted order. You can use a worksheet Change macro to accomplish.

    In E2, put =A2

    and in F2, put = B2

    Then, copy E and F down for a few. If you have Excel 2002 or later it will keep adding as you add to A and B.

    Right-click on the worksheet tab, and select View Code.

    The VBE window will appear. Then paste in this code.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim VRange As Range
        Set VRange = Range("B2:B" & Range("B65536").End(xlUp))
        If Not Intersect(Target, VRange) Is Nothing Then _
        Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End Sub
    Adjust to meet your needs.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Unhappy

    Dear Shades,

    I tried the solution you suggested, but i faced this problem
    run-time error 1004
    Application-defined or object-defined error

    Is it possible to send me a simple excel sheet with this solution, cauase i'm a beginner in excel coding.

    thanx

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Okay, here is a sample. I put formulas in columns E and F. Be sure that you go to Tools > Options > Edit, and check the box "Extend List formats and Formulas".

    Now when you add a name in column A (next one should be A14, then E14 will automatically fill in. Then when you add a number in B14, the macro will automatically sort Columns A:B, and Columns E:F.

    To view the code, right click the worksheet name, and select View Code. The VBE window will appear.

    -------------------------------------------
    BTW, if this isn't quite what you need, post back and we can modify it based on your needs.
    -------------------------------------------
    Attached Files Attached Files
    Last edited by shades; 09-08-04 at 17:42.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Exclamation

    Dear Shades,
    thanks for the assistance, and i'm sorry for not responding, i was working outside the office, i tried to modify a value in the excel sheet but i faced this error:
    compile error:
    variable not defined
    (x1SortNoraml).

    what shall i do?

    Thanks in advance
    Giggs

  6. #6
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    what version of excel are you using i use 2000 to sort this change shades sort to

    Code:
       Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Header:=xlGuess, _
            OrderCustom:=1, MatchCase:=False
    Dave

  7. #7
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Thumbs up

    Hi,

    I tried Dave suggestion, and it's working just fine.

    Thanx Dave
    Thanx Shades

    best regrads
    Giggs

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Glad that it is working.

    Thanks, Dave. I use XL 2002, and don't have the opportunity to test on other versions.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

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