Sorting problem

giggs11 · #1 (**permalink**) 09-07-04, 13:47

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

shades · #2 (**permalink**) 09-07-04, 17:55

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.

giggs11 · #3 (**permalink**) 09-08-04, 11:20

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

shades · #4 (**permalink**) 09-08-04, 16:38

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

giggs11 · #5 (**permalink**) 09-13-04, 10:49

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

DavidCoutts · #6 (**permalink**) 09-13-04, 10:58

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

giggs11 · #7 (**permalink**) 09-13-04, 11:16

Hi,

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

Thanx Dave
Thanx Shades

best regrads
Giggs

shades · #8 (**permalink**) 09-13-04, 15:17

Glad that it is working.

Thanks, Dave. I use XL 2002, and don't have the opportunity to test on other versions.

Internet Services

Web Development

Digital Marketing

Consumer Tech