Automate a column

[Tenspeed39355]

Is there a way to have a column of data to change positions if there is a change
in the data the column receives from another column? Example: I have a column
of stock fund symbols and the percent they represent. Is there a way for the
symbols and percents to move up or down the column without doing a sort.
Thanks
Max

[Jacob Hilderbrand]

You will probably need to sort, but you can do it automatically:


Option Explicit

Private Sub Worksheet_Calculate()
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

But if the values are put in with formulas this won't work.

[johnske]

If you mean to have the spreadsheet sort the values in one column (say column A) into descending order in another column (say column C), put this >>
in C1, =LARGE(A$1:A$5,1)
in C2, =LARGE(A$1:A$5,2),
in C3, =LARGE(A$1:A$5,3)....etc :bink:

EDIT: If you don't want to restrict the number of rows, use this >>
in C1, =LARGE(A:A,1)
in C2, =LARGE(A:A,2),
in C3, =LARGE(A:A,3)....etc

[Tenspeed39355]

Thanks for the reply. What I need is for the stock symbols and percent to change
positions in columns A & B when the percent changes without doing a sort.
Example
Column A Column B
ASG 10%
AOF 15%

I would like for AOF and the 15% to move to first place automaticly when new data
is entered from another source. I have a program that changes the Mkt price in
another column every 10 seconds. I have column B attached to that so that Column
B will change the percent.I want the symbol and the percent to move to fist place
if that is the case so that the highest percent and symbol move together.
so that the highest percent and symbol will be in first place and the rest will be in
decending order.
max

[Jacob Hilderbrand]

Try a sort macro on either the Change or Calculate event. That way it will trigger with new data and reorganize your data.

[Tenspeed39355]

I have no expertise using macros. Can you give me an example of where to start.

THANKS
Max

[johnske]

Hi Max,

What Jacob's getting at is something like the code below, the best way to find out what it does is to copy the code, then open a workbook and select Tools/Macro/Visual Basic Editor.

This places you in the visual basic editor (VBE) window - in the VBE window, select View/Project Explorer - you will now see the project explorer pane... double-click Sheet1 inside that pane and a code pane will appear to the right of the project explorer pane. Paste the copied code into that pane.

Now go to the X button (top right) in the VBE window and you will return to excel. (Better 'save' now)

Now (on sheet1) type some "names" in column A and some values in column B and watch what happens each time you change a value.......

Regards,
John :bink:


Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Columns("A:B").Select
Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
Application.ScreenUpdating = True
End Sub

[Tenspeed39355]

Thanks guys that works just great
MAX