Tech Support Guy banner
Status
Not open for further replies.

Solved: Excel macro required for a sort problem

3K views 26 replies 2 participants last post by  zantelover 
#1 ·
I wonder if anyone can help with the following.

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.
 
See less See more
#2 ·
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.

happy recording :)
 
#3 ·
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.

Yours in frustration !
 
#4 ·
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.
 
#5 ·
Hans

Many thanks for the offer of help !

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 !

Look forward to hearing from you.

Terry
 

Attachments

#6 ·
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
 

Attachments

#7 ·
Hans

Thank you so much for that !

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 !

Regards

Terry
 
#8 ·

Attachments

#9 ·
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 ?
 
#10 ·
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.
 
#11 ·
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.
 
#15 ·
Maybe you mean that the sheet is protected, okay, right click on the sheet's tab and select Unprotect

That's what I told you, the sorting macro unprotects the sheet and protects it again afater sorting.

Ther's no password, I explained that also.

Simple basic Excel functionality
 
#18 ·
Hans

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.

Regards

Terry
 

Attachments

#19 ·
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
 
#21 ·
Hans

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 !

Regards

Terry
 
#25 ·
Thanks once again for all the help , Hans !

My query is fully complete and I have now embodied the coding into my full workbook and everything works fine !

I have also received my book "Excel VBA programming for dummies" which I will work through and hopefully improve my limited ability.

A Happy New Year to you and all our readers !
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top