Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Script for sorting
hi all,
I need some help... I would like to have a script that can do some sorting for me Col A - Region Col B - Area Col C - Store Col D - Trading Department Col E - Fine Department Col F - Reference Number Col G - Item Description Col H - Base FC Col I - Uplift Col J - Demand Col K - Display Col L - Planning First of all, I would like to create a button that will allow me to sort the Col F and Col G in a ascending order, then on col L (planning), it would show me if the store has done any planning, basically it would be using the following forumala =IF(I2=J2,"Unreviewed","Reviewed") Also, can I fill the ones that say "Unreviewed" with a color (yellow) sorry if i am asking too much...hope someone can help me... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Script for sorting
You are not asking for too much! Forgive me if I give you too detailed
instructions, I do not know your proficiency level with macros and coding... 1. Automatic sorting There is a feature called "Record macro" in the menu Tools Macro that allows you to record in a macro what you have typed on the keyboard. If I select the range A1:L22, click on the menu Data Sort, then select column F, G and L, with a header row, then stop macro recording; then I press ALT+F11 to open the visual basic editor, then open the Module1 on the left pane, I get: Sub Macro1() ' Macro1 Macro ' Macro recorded 04-08-2007 by SQ Range("A1:L13").Sort Key1:=Range("F2"), _ Order1:=xlAscending, Key2:=Range( _ "G2"), Order2:=xlAscending, _ Key3:=Range("L2"), Order3:=xlAscending, _ Header:=xlYes, OrderCustom:=1, _ MatchCase:=False, Orientation:= _ xlTopToBottom, DataOption1:=xlSortNormal, _ DataOption2:=xlSortNormal, _ DataOption3:=xlSortNormal End Sub So each time I will execute Macro1, I will sort the range accordingly. I suggest that you modify the range and put a larger one (something like "A1: Z5000") in order to accommodate growth of your spreadsheet. Now, call the toolbar for the button (menu View Toolbar Control Toolbox). Click on the button icon and draw the button (click down and make a rectangle while still pressing the left mouse button down) where you want it. Right- click on the button and select Properties. In the new Window, change the caption field into "Sort Me!" (without quote), or whatever text you like. Now, go back to the Visual Basic window, click on the sheet Sheet1 in the left pane, then select "CommandButton1" in the first combo box. Automatically, Excel will create some code: Private Sub CommandButton1_Click() End Sub In between these two lines, simply type Macro1. This means that each type that the button is clicked, the program macro1 will execute. As macro1 is the program that sorts the sheet, you are (almost) done! Go back to the spreadsheet and click on the button to test it... but nothing happens. This is because you are still in Edit mode of the button, so you should exit it by clicking the top left icon on the toolbar Control Toolbox (with a square, ruler and pen). Now, if you click on the "Sort Me!" button, the spreadsheet is sorted. 2. Yellow cell For automatic formatting, use conditional formatting. For that, select entire column L, then click on the menu Format Conditional Formatting. On the new window, select or type "Cell Value Is", "Equal To", "Unreviewed" (all without quotes), then click on format button, select the third tab "Pattern", then choose the yellow color, and confirm. You can also use the autofilter option (select the first row, then menu Data Filter Autofilter), then selecting "Unreviewed" in column L will only show you these lines, useful for printing for example. Hope this helps, Stephane Quenson. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200708/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with the VB script | Excel Worksheet Functions | |||
I need some VB script please | Excel Discussion (Misc queries) | |||
VB script help - please!! | Excel Discussion (Misc queries) | |||
VBA script help..Please !!!! | Excel Discussion (Misc queries) | |||
VB script help..please !! | Excel Worksheet Functions |