1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel macro required for a sort problem

Discussion in 'Business Applications' started by zantelover, Dec 17, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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.
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     

    Attached Files:

  7. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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 [​IMG]
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans

    Attached Files:

  9. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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.
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    I did


    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.


    select the userform and press F5 to activate (test)
     
  13. zantelover

    zantelover Thread Starter

    Joined:
    Jan 2, 2009
    Messages:
    168
    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.
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Then you should enable editting, thats your system's settings, the file is not read only
     
  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1031633

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice