how to write a macro for sorting in excel?

Question

im writing a spreadsheet that standings for the upcoming football season. but i cant figure out a way for the standings to "update" in accordance to changes in record. the sort feature will sort it only once and i need a macro that will do it automatically

Answer 1

It is a little bit more complex than recording a macro. Here is one way to do it. The following example assumes:



The Team Names are in column A beginning in row 2

The number of wins is in column B beginning in row 2

The number of losses is in column C beginning in row 2



Enter Headings in A1:E1:



Team

Wins

Losses

Pct

GB





Enter all Team names in column A.



Select cells D2 through the last row containing a team name in column A.



Format the cells as Number, with 3 decimal places.



In cell D2 enter the formula:



=IF(AND(B2<>"",C2<>""),B2/(B2+C2),"")



Drag the formula down through the last row containing a team name.



In Cell E3 enter the formula:



=IF(B3="","", IF(SUM($B$2-B3+C3-$C$2)=0, "",(($B$2-B3)+(C3-$C$2))/2))



Copy this formula down through the last row containing a Team name in column A.



Next, copy the following code to the clipboard:



Private Sub Worksheet_Change(ByVal Target As Range)

Dim LastRow

curCell = ActiveCell.Address

LastRow = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A2:C" & LastRow)

If cell.Value = "" Then

Exit Sub

End If

Next

Application.ScreenUpdating = False

Range("A2:D" & LastRow).Select

Selection.Sort Key1:=Range("D1"), Order1:=xlDescending, _

Key2:=Range("A1"), Order2:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal, DataOption2:=xlSortNormal

Range(curCell).Select

End Sub





Select the appropriate worksheet and right click the sheet tab.



Select 'View Code'



Paste the code into the sheet module editing area to the right.



In the menus at the top of the VBE, select INSERT > MODULE



Copy the following code and paste it into the newly created module;



Public curCell



Close the VBE and return to the worksheet.



Enter your wins and losses. Once each team has a won/lost record, the macro will automatically sort by the Won/Lost percentage calculated in column D.



The 'Games Behind' will be calculated and displayed in column E.



If two teams have the same record, they will be sorted alphabetically by team name.



If, by some chance you need to add a team, the macro will self adjust to the new sort range. You will, however, have to drag the formulas in columns D and E down through the new last row.



I have tested this and it works appropriately. Again, the macro will not initially sort until there is a value in every team's Wins and Losses column.

Answer 2

Here is one macro that will sort columns AN:BQ in descending order. You don't state whether you copy data into the same workbook/worksheet or are dealing with a new workbook each time. If it is a new workbook each time, you might want to consider using the Personal.xls method to eliminate the need to create the macro in each new workbook. If you are copying data into the same workbook: Copy the following macro to the clipboard: Sub SortANtoBQ() Application.ScreenUpdating = False For Each cell In Range("AN1:BQ1") cell.Select alphacol = Left(cell.Address(0, 0), 1) & Mid(cell.Address(0, 0), 2, 1) Columns(alphacol & ":" & alphacol).Select Selection.Sort Key1:=Range(alphacol & 1), Order1:=xlDescending, _ Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Next Range("AN1").Select End Sub Press ALT + F11 In the menus at the top of the VBE, select INSERT > MODULE Paste the macro into the editing area to the right. Close the VBE and return to the worksheet. Press ALT + F8 When the Macros window opens, highlight the macro and click 'Options...' Enter a letter to be used as a keyboard shortcut and click 'OK'. Close the Macros window. To sort the data, press CTRL + your shortcut letter. ================ If you are dealing with a new workbook each time, try the Personal.xls method: Create a new folder in the root C:\ drive and name it 'Personal' (no quotes) Open a new blank workbook and perform the above procedure in the new workbook. Save the workbook to the folder created in the C:\ drive, naming it 'Personal' (no quotes) Note: if using Excel 2007 or 2010, save it as a 'macro enabled workbook'. Then For Excel 2003: =========== Open Excel and go to Tools > Options and select the General tab. In the 'At startup, open all files in:' textbox enter: C:\Personal Click 'OK' For Excel 2010: =========== Go to File > Options and select the Advanced options. Scroll down to the General options (near the bottom). In the 'At startup, open all files in:' textbox enter: C:\Personal Click 'OK' Now, each time you open any Excel workbook, the Personal workbook will be opened in the background. All macros contained in the Personal workbook are available to any open workbook, using the keyboard shortcut you created in the Personal workbook. Open your 'data' workbook and press CTRL + your shortcut letter when you need to sort the data.

Answer 3

Record a macro to do what you want. It should be straight forward.