Results 1 to 3 of 3

Thread: Help with code

  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Help with code

    Hi All,

    I have a code using record macro. I need to include a step where if the auto filter return record above (say) 10, a message is generated saying something like "result is greater than 10 - do manual entry" with a cancel button to stop the macro running.

    I assume that the section of the code below is that part that this would look at the number returned. Also is there away to set the filter criteria in the code rather than the autofilter?

    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    ActiveWindow.ScrollWorkbookTabs Position:=xlLast
    Sheets("Rec inj and cat 3 above import").Select
    range("A8:J8").Select
    Selection.AutoFilter
    ActiveSheet.range("$A$8:$J$52").AutoFilter Field:=7, Criteria1:=Array( _
    "1. FAI", "Report Only", "="), Operator:=xlFilterValues
    ActiveSheet.range("$A$8:$J$52").AutoFilter Field:=6, Criteria1:=Array( _
    "3 - Moderate", "4 - Major", "5 - Catastrophic"), Operator:=xlFilterValues
    ActiveWorkbook.Worksheets("Rec inj and cat 3 above import").AutoFilter.Sort. _
    SortFields.Clear
    ActiveWorkbook.Worksheets("Rec inj and cat 3 above import").AutoFilter.Sort. _
    SortFields.Add Key:=range("F8:F52"), SortOn:=xlSortOnValues, Order:= _
    xlDescending, DataOption:=xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("Rec inj and cat 3 above import").AutoFilter. _
    Sort
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply


    I hope that all makes sense - thanks for any suggestions

    Regards
    Last edited by verada; 2015-04-20 at 01:39. Reason: Added question about autofilter

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    All of that should be doable but may I suggest using FINDNEXT instead to find each instance and prompt you to correct. Attach file with complete explanation,
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Verada,

    We'd be able to offer better advice if you provided some sample worksheets with a text explanation of what you are trying to accomplish.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •