I have a table , A8:V36. Peoples names appear from cells B9:B36.
Dates are entered from C8:V8 with a range name defined as 'Date'.
Integer values appear in cells C9:V36
I want a macro to be able to sort the table by selecting one of the dates and sorting in descending order of value against each person for the date just highlighted.
If I were to do the job manually , I would highlight the table with the 1st Row of the highlighted area including the headings "Name" , "Date1" , Date 2" , etc up to "Date 20".
If I then clicked Data then Sort , a dialogue box would appear. Possible inputs to it , reading from Left to Right would be 'Column [Sort By]' , 'Sort On[Values etc]' and 'Order[e.g A to Z]'
In the 'Sort By' box a drop down list appears containing all the headings of each column. This includes whichever date you require to sort the underlying values by.
So what would the macro script be to achieve the presentation of that dialogue box , allow input to it and then activate the sort once the user had clicked OK ?
I hope that I have made the application clear. Help would be appreciated.
Hi,
My first advise and at the same time will help building vba knowledge is to record a macro.
Start the macro recorder and do just what you have explained in the post.
Make sure that the macro is svaed in the Current Workbook.
After you finished and stopped the recording you can see what if is that was done.
If you want to see only that date and then sort, then the first step would be to set the filter mode, select the date you want and then sort the serults, alls this in the macro.
Adding an input box to enter a particular date is a little more work but then you already have the basic code.
Let me know if you get it working.
I'll gladly look at the generated code and help you 'polish' it.
Thanks for that Keeballah but it doesn't help me at all !
As I explained , I have already been able to record a macro for doing the routine for ONE date and the code for doing that is
SortWeekResults Macro
'
' Keyboard Shortcut: Ctrl+a
'
Range("B2:V30").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("J3:J30") _
, SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B2:V30")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
End Sub
from which we can see that I have selected the specific date which appears in J2 and the range J3:J30 is all the underlying data in the table under that date which is sorted from Highest to Lowest.
THis works fine but is limited the date appearing in J2 .................. but I have dates appearing from C2 to V2.
You then go on to say ,
Adding an input box to enter a particular date is a little more work but then you already have the basic code.
............. that is my problem , I do not know what changes to make to enable me to add an input box from which ONE of the TWENTY dates can be selected.
It's not that hard but, can you post a sample of your workbook, lets say 10 rows with a couple of dates and the macro code you use.
I'll take a shot at editting it and see if I can come up with a solution.
As I understand the soirting is in Column J where the dates are storen, you want to selcet one date and then sort all the names too? or just filter on the dates?
You did'n mention it but I can see from your VBA code that you're using Excel 2007 or 2010, but this information should always be given since not all VBA code works in all versions.
I am attaching the spreadsheet from which you will , I think , understand what is required. The whole table needs to be sortable by any of the dates which appear from C2 to M2. Each persons score for all dates obviously need to be retained irrespective of which date the table has been sorted on.
Hope you can understand !
I had to include some extra vba code because dates in Excel are terrible
As you're using the Ducth date format 1-Okt-2012 with is 1-10-2012 is recognized as 10-1-2012 which does not tally.
I found some vba code sometime ago with which I got control of the dates.
Well, I included a little userform where you pick the date and the cells are sorted.
You may keep on adding dates and the macro will follow, no problem there. even the rows are dynamic.
I automatically color the date chosen every time you choose a different date to sort
The sorting code is written for Excel 2003, I don't have 2007 or 2010 active right now but it works in any verison
Yes ! It works fine except that I had intended having the worksheet to whcih the macro applies as a 'Protected' sheet and the macro does not work when I protect it - fine otherwise.
I deleted your sub to take account of the date format and the macros still work OK.
I note that one of my original bits of coding , namely Add Key:=Range("J3:J30") , is still in your revised version even though this is no longer needed because you are now able to SELECT the date required !
I can honestly say that I do not understand much about the coding that you have derived and the intricacies of the UserForm are still a mystery to me which is why I am thinking about buying a book entitled ... Excel VBA Programming for Dummies.
The challenge now is to transfer your coding into the particular worksheet of my 36 sheet workbook and get it working !
Thanks once again .... and I hope that you may be available on-line for further consulation !
Thanks for all that , Hans , but you are confusing me !
You said "Had to add a button to the userform to make sure the sheet remains protected." Where can I see that in the spreadsheet so that I can understand how to do it for myself ? How do you display the Userform ?
When you open the VBA editor and select the forms you'll see the userform, there you can change it etc.
You can select the object or the code.
The object is the iser form itself and the code is vba code that the userform uses.
If you need, I'll try and put some simple steps in a word doc and mail it.
I would appreciate some SIMPLE steps in a Word document , thanks.
You have not answered the question I put to you in the last email about the 'button' that you put in and I still have problems in displaying the UserForm.
I am afraid that you will need to treat me as a novice as far as VBA is concerned and explain things very simply.
The file that you sent me - Trial macro sorting .xlsm - I now want to incorporate into my full workbook and adapt it to the larger sized table there.
But the file you sent me is Read Only and I am having problems using it properly.
Thanks for all that , Hans. You have solved my problem and given me the coding for my application , all I have to do now is to apply it to my full-size spreadsheet.
I trust that I can do that without any further help.
I always do believe in myself ............ when I have the knowledge !
However , I have copied across your forms and modules to my full-size workbook .... and guess what ? ..... It doesn't work !........... and I don't have the knowledge to understand why !
I get a "Run-time error '9'. Subscript out of range." message.
When I come to de-bug it , it seems to indicate that in the SubRoutine SubWeekResults , there is something wrong with the code 'SortPrompt.Show
My FULL Workbook is too big to send to you on this Forum , so I am attaching the ONE spreadsheet of the WorkBook to which our considerations have been directed. I hope that you would be so kind as to have a look and see what the problem is.
When you move a row like the date from row 2 to row 8 ....
Check the Userform_Initialize whre you see two references to row 2
The same in the sort module.
I have to run right now but I think you could figure it out I marked the libnes that need attention and hopefully explain themselves
Code:
Sub SortWeekResults2(sCol As String)
Dim lastRow As Long, lastColumn As Long
lastRow = Range("B" & Rows.Count).End(xlUp).Row
lastColumn = ActiveCell.SpecialCells(xlLastCell).Column
[COLOR="Red"] Range("B2:" & C2R(lastColumn) & lastRow).Sort Key1:=Range(sCol & "3"), Order1:=xlDescending, _
Key2:=Range("B3"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _[/COLOR] :=xlSortNormal
Range(sCol & "2").Interior.Color = vbCyan
End Sub
Code:
Private Sub UserForm_Initialize()
Me.Top = (Application.UsableHeight / 2) - (Me.Height / 2)
Me.Left = (Application.UsableWidth / 2) - (Me.Width / 2)
[COLOR="Magenta"] Me.PromptBox.Clear
lstCol = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
ActiveSheet.Unprotect
Range(Cells(2, 3), Cells(2, lstCol)).Interior.ColorIndex = xlNone
ReDim arr(1 To lstCol - 2, 2)
For Each myRange In ActiveSheet.Range(Cells(2, 3), Cells(2, lstCol))
[/COLOR] i = i + 1
arr(i, 1) = i + 2
arr(i, 2) = GetDateInLocalFormat(myRange.Value)
Next myRange
For i = LBound(arr, 1) To UBound(arr, 1)
With Me.PromptBox
.AddItem arr(i, 2)
End With
Next i
Me.PromptBox.SetFocus
End Sub
I spent a couple of hours trying to understand the syntax and then changing those terms which applied to rows , all to no avail. I got absolutely nowhere ! Most frustrating but no doubt easy when you know how.
Come back to me after Xmas with finished code as a belated Xmas present !
A Happy New Year to you too and I have attached some "homework" now you the the"Excel VBA programming for dummies"
I did not include the editted VBA code, just the instructions of how to add a abutton and make some small changes, If you get stuck, let me now and I'll help.
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!