Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need help/explantion of the macro in the attached file. I am trying to understand how this macro works.

    The buttons at the top of the file will: 1) filter all lines where the Out Of Tolerance numbers are less than -0.001 or greater than 0.001. 2) display all lines.

    The fomulas in Col. J and L evaluate the difference in the normal dimensions (Col F) and the actual dimensions (Col I) and then calculate how much out of tolerance there is.

    I discovered that the data starting in column Z was hidden; I formatted it to blue. Starting at AI21 is a formula that counts the number of times an "out of tolerance " number displays in the Cols. JLNP. Then in Col. AJ21 is a formula that looks at the displayed value in AI21; If any number except 0 is displayed in AI 21, then a 1 is displayed in the cells of col AJ. (This has to be the trigger for filtering)

    Then I found that the cells in cols AI and AJ are referred to as the List Range in the Advanced Filter dialog box; but I didn't see a criteria range. I also noticed that AutoFilter is turned on but don't see the drop-list buttons, but perhaps they don't display in this type of filter.

    This is the code for the 2 buttons that run the macros:

    Sub Button94_Click()
    '
    ' Button94_Click Macro
    ' Macro recorded 2006-7-4
    '

    '
    ActiveWindow.SmallScroll Toleft:=1
    Selection.AutoFilter Field:=2, Criteria1:="1"
    End Sub

    Sub Button95_Click()
    '
    ' Button95_Click Macro
    ' Macro recorded 2006-7-4
    '

    '
    ActiveWindow.SmallScroll Toleft:=11
    Selection.AutoFilter Field:=2
    End Sub


    I do not understand how that code is interacting with the Avanced filter range to make these macros work. I am not familiar with the SmallScroll command and how it applies in this example. I do see the criteria of "1" which has to refer to Col. AJ. It seems I'm missing a piece of the puzzle.

    Would really appreciate some clear, detailed explanation of this so I can re-create this.
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Looking at the Data you have

    The Range to which the filter is being applied is

    $AI$20:$AJ$880

    The line

    Activewindow.SmallScroll Toleft:=1


    Does nothing at all and could be deleted.


    The line that does the filtering is


    Selection.AutoFilter Field:=2, Criteria1:="1"


    This is applying filtering to the Range $AI$20:$AJ$880


    It is Checking Column 2 of the Filtered Range which is Column AJ
    It is filtering the list to cases where this is a 1


    Because this is adjacent to the main list, the effect is to also filter the main list.




    In the second button the Scroll Line again does NOTHING.
    It was recorded and they scrolled as part of the recording.


    The Line


    Selection.AutoFilter Field:=2


    Removes the applied filter to all Rows.


    The danger with the macro as it stands is that should someone create
    a different filter manually, then the filtered range selection MIGHT change and the macro would no longer work.



    IF the size of the range does not change then it might be better to edit the macro so that it references the specific range.


    e.g.


    Range("$AI$20:$AJ$880").AutoFilter Field:=2, Criteria1:="1"


    and


    Range("$AI$20:$AJ$880").AutoFilter Field:=2




    or better still name this range and use the range name


    NOTE...


    These macros have NOTHING to do with ADVANCED Filters.
    They are using the built in AutoFilter





    Andrew

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks!!

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have another question about formulas in this file.

    In Column J, there is a nested IF statement. I do not understand what the purpose of the question mark is. It looks like the first part of the IF statement is checking to see if a cell is blank. For instance at cell J43, it checks I43 to see if it is blank. Since it is, I understand the True part of the IF statement is to leave J43 blank. Same scenario at J54. Those two rows appear to be blank rows in the worksheet.

    At J62, there is data in that row, but at I43, the Actual Dimension number is blank.

    It seems that first nested statment is checking the cell again to see if it is blank, but don't know what the pupose of the ? mark is.

    Help??

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The data showing at J62 is actually a text box that was added to the sheet. Click on the Wrong Features? and you will see sizing handles.
    Attached Images Attached Images

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The question mark would appear if the Column I was not blank, but the F column was blank.

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks! Your explanation about column F being blank and Col I not being blank answers the ? mark problem. I was aware that there was a text box floating over J62; the contents of J62 contains the If statement.

    Thanks!!!!

Posting Permissions

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