Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 39 of 39

Thread: AutoFilter Percent Values +-5%

  1. #21
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    This is a shot in the dark: try removing the line:
    .Range("A1:AJ1").AutoFilter
    altogether.

    Otherwise, we could do a TeamViewer session (do an internet search)
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  2. #22
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by p45cal View Post
    This is a shot in the dark: try removing the line:
    .Range("A1:AJ1").AutoFilter
    altogether.
    On the other hand, don't.

    In your code:
     .Range("A1:AJ1").AutoFilter
        rng.AutoFilter Field:=6, Criteria1:=">="…
    .Range("A1:AJ1") is the header row, but rng is the data body excluding the headers.
    Normally, I'd expect the two .Autofilter lines to act on the same range. So for consistency, I'd have
    Set rng = ws2.Range("A2:J" & lr)
    changed to:
    Set rng = ws2.Range("A1:J" & lr)
    and later, change:
    With ws2
      .AutoFilterMode = False
      .Range("A1:AJ1").AutoFilter
      rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
    End With
    to just:
    ws2.AutoFilterMode = False
    rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #23
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Looking further into this, it very much looks like this is a problem with comma v. dot for the decimal separator. It seems to be a problem which changes from version to version of Excel.
    I'd like to help but since there are loads of questions and things I could ask you to try, it would take far too long, especially as you're in a different locale from me, - If you could arrange for us to have a TeamViewer session (or similar) I think it would be faster.
    Last edited by p45cal; 09-02-2020 at 01:09 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #24
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Seems to be sufficient:

    Sub StatTest()
        Range("F132:F146").NumberFormat = "0%"
        
        With Workbooks("Book1.xlsm").Sheets("Sheet1").Cells(1).CurrentRegion.Resize(, 10)
             .AutoFilter 6, ">=" & [Max(F132:F146)] - 0.1, xlAnd, "<=" & [Max(F132:F146)] + 0.05
        End With
    End Sub

  5. #25
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    snb, if that works then - 0.1 would need to be - 0.05
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #26
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    p45cal, I think you are on to something about the comma and dot. I've been experimented for a day now and something is odd. I told you before it works with numbers but I found out that is only partly true. If Max value (I guess) is less than zero it doesn't work. I multiplied all column F (both workbooks) values by 10, formatted as "0" and it worked. But that doesn't help me with my percent problem.
    I haven't got any e-mail notifications since post 21. I logged in just now to tell you about this. Hence you haven't got any answers from me.


    SNB, I will try your suggestion this afternoon.

  7. #27
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    SNB, I modified as p45cal suggested but I get same result: Blank sheet (except row 1). I click dropdown menue col.F > click "Number Filters" > click "Between..." > Check values >=0,2779 and <=0,3779 (they are there) > click "OK" button and there I have my filtered list!

  8. #28
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    p45cal, I made changes according to your post 22 but I get same result. Only if I click dropdown menue col.F > click "Number Filters" > click "Between..." > Check values >=0,2779 and <=0,3779 (they are there) > (here comes the odd thing) click "OK" button, I get my filtered list!

  9. #29
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
        With Sheet1.Cells(1).CurrentRegion
             .AutoFilter 6, ">=" & [10^4*Min(O10:O20)] & "%", 1, "<=" & [10^4*Max(O10:O20)] & "%"
        End With
    End Sub

  10. #30
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by p45cal View Post
    If you could arrange for us to have a TeamViewer session (or similar) I think it would be faster.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  11. #31
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    FWIW, it seems to work OK here



    Capture.JPG


    Book1.xlsm is 'ThisWorkbook' with the macros and data. It's possible that using ActiveWorkbook didn't return the workbook that you thought it was

    Book2.xlsx is the one with "F132:F146" values


    Option Explicit
    
    
    Sub StatTest()
        Dim wb1         As Workbook, wb2 As Workbook
        Dim ws1         As Worksheet, ws2 As Worksheet
        Dim rng         As Range, rng9   As Range
        Dim lr          As Long
        Dim MaxValue    As Double
        Dim MinValue    As Double
        
        
        Application.ScreenUpdating = False
        
        Set wb1 = ThisWorkbook
        Set ws1 = wb1.Sheets("Sheet1")
        
        Set wb2 = Workbooks("Book2.xlsx")
        Set ws2 = wb2.Sheets("Sheet1")
        
        lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
        
        Set rng = ws1.Range("A2:J" & lr)            'Made used range smaller for easier overview. Still same problem.
        Set rng9 = ws2.Range("F132:F146")
        
        rng9.NumberFormat = "0%"                    'To show and make sure rng9 and Col.F have same format.
        ws1.Range("F2:F" & lr).NumberFormat = "0%"  'To show and make sure rng9 and Col.F have same format.
        
        
        MaxValue = Application.WorksheetFunction.Max(rng9)
        MaxValue = MaxValue + 0.05
        MinValue = MaxValue - 0.1
        
        With ws1
            .AutoFilterMode = False
            .Range("A1:J1").AutoFilter
            rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue    'Works for NumberFormat= numbers, not NumberFormat= %
        End With
        
        Application.ScreenUpdating = True
    End Sub


    Matter of personal style, but I'd rearrange the macro to group the workbook processes together

    Option Explicit
    
    
    Sub StatTest()
        Dim wb1         As Workbook, wb2 As Workbook
        Dim ws1         As Worksheet, ws2 As Worksheet
        Dim rng         As Range, rng9   As Range
        Dim lr          As Long
        Dim MaxValue    As Double
        Dim MinValue    As Double
        
        
        Application.ScreenUpdating = False
        
        Set wb1 = ThisWorkbook
        Set ws1 = wb1.Sheets("Sheet1")
        lr = ws1.Cells(Rows.Count, "A").End(xlUp).Row
        Set rng = ws1.Range("A2:J" & lr)            'Made used range smaller for easier overview. Still same problem.
        ws1.Range("F2:F" & lr).NumberFormat = "0%"  'To show and make sure rng9 and Col.F have same format.
        
        
        Set wb2 = Workbooks("Book2.xlsx")
        Set ws2 = wb2.Sheets("Sheet1")
        Set rng9 = ws2.Range("F132:F146")
        rng9.NumberFormat = "0%"                    'To show and make sure rng9 and Col.F have same format.
        
        
        MaxValue = Application.WorksheetFunction.Max(rng9)
        MaxValue = MaxValue + 0.05
        MinValue = MaxValue - 0.1
        
        With ws1
            .AutoFilterMode = False
            .Range("A1:J1").AutoFilter
            rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue    'Works for NumberFormat= numbers, not NumberFormat= %
        End With
        
        Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #32
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    snb, post 29, made Max- and min value =0


    Paul, post 31, "Set wb2 = Workbooks("Book2.xlsx")", both workbooks are .xlsm and can't be changed. I tried with .xlsx but no difference. I tried your code but I get same result as before = filtered but doesn't show until I click the OK button. I also tried with "ThisWorkbook".


    p45cal, I am not allowed to use TeamViewer. I tested with Macro Recorder, inserted Max- and Min Values manually, and it works!

    Sub Macro4()
    '
    ' Macro4 Macro
    ' Recorded in Book1
    ' Manually insert of Max- and Min values.
    
    
        'This works (with ",").
        'Result: Filtered sheet shows as expected.
        Range("A1:J1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$J$40").AutoFilter Field:=6, Criteria1:=">=0,2779" _
            , Operator:=xlAnd, Criteria2:="<=0,3779"
        Selection.AutoFilter
        
        'This works not (with "."). Not even if I click "OK" button.
        'Result: Blank page except first row
        Range("A1:J1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$J$40").AutoFilter Field:=6, Criteria1:=">=0.2779" _
            , Operator:=xlAnd, Criteria2:="<=0.3779"
    End Sub
    One observation: Max value show value with ",".
    I have tried: 1. +"0,05" 2. +0.05 and 3. +(5/100).
    All with same result: Blank page except first row, click "OK" button and filtered sheet shows.

  13. #33
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Then as a quick fix you could do something like this:
    Earlier in the code change:
    Set rng = ws2.range("A2:AJ" & lr)
    to:
    Set rng = ws2.range("A1:AJ" & lr)
    then
    MaxValue = Application.WorksheetFunction.Max(rng9)
    MaxValue = MaxValue + 0.05
    MinValue = MaxValue - 0.1
    crit1 = Replace(">=" & MinValue, ".", ",")
    crit2 = Replace("<=" & MaxValue, ".", ",")
    
    ws1.AutoFilterMode = False
    rng.AutoFilter Field:=6, Criteria1:=crit1, Operator:=xlAnd, Criteria2:=crit2
    It should NOT matter what format the numbers are in (neither rng9 nor column F, they won't need to be similar formats either).
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    What do you get if in the Immediate pane you type:
    ?">=" & 3.3
    and press Enter?
    >=3.3
    or
    >=3,3

    ?

    To make my last suggestion work in more locales consider also:
    crit1 = Replace(">=" & MinValue, ".", Application.International(xlDecimalSeparator))
    crit2 = Replace("<=" & MaxValue, ".", Application.International(xlDecimalSeparator))
    Last edited by p45cal; 09-06-2020 at 12:38 PM.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #35
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Paul, post 31, "Set wb2 = Workbooks("Book2.xlsx")", both workbooks are .xlsm and can't be changed. I tried with .xlsx but no difference. I tried your code but I get same result as before = filtered but doesn't show until I click the OK button. I also tried with "ThisWorkbook"
    xlsx or xlsm doesn't make any difference. Still works just fine without having to hit OK.

    What are you regional settings? Possible there's a conflict so 39,5 would be a string and wouldn't work with numbers

    I vaguely recall that Filter Criteria only works with US Dates and US Number formats

    So if I open a Swedish formatted workbook with a number format 123,45 Excel converts it to 123.45 for my US format
    Last edited by Paul_Hossler; 09-06-2020 at 11:04 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  16. #36
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    p45cal, Paul,
    Windows10 National settings: Decimal separator= ","
    Excel(2019) settings: Decimal separator= ","
    I have not tested to change them. I'm afraid it will mess up other workbooks.


    ?">=" & 3.3 shows >=3,3 in the Immediate pane.
    With crit1 and crit2 Dim As Double I get: Run-time error '13' ,Type mismatch, at line: crit1 = Replace(">=" & MinValue, ".", ",") or crit1 = Replace(">=" & MinValue, ".", Application.International(xlDecimalSeparator)).


    'Dim crit1 (and crit2) As Double, Error
    'Dim crit1 (and crit2) As String does filter but have to click "OK" button for filtered sheet to show.
    Dim crit1 (and crit2) As Variant does filter but have to click "OK" button for filtered sheet to show.
    All three show Min- and Max values: >=0,2779 and <=0,3779 in code and dropdown menue.


    I multiplied (with PasteSpecial) rng and rng9 with 10 and format as number.
    Changed in code: MaxValue +0.5 MinValue -1


    Run:
    ws2.AutoFilterMode = False
    rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
    MaxValue show: 3,779 and MinValue show 2,779 in code BUT in: dropdown menue col.F > click "Number Filters" > click "Between..." > values are >=2779 and <=3779!


    I also tried code on another computer with Excel 2016 but same result.

  17. #37
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Then I'm at a loss.
    Quote Originally Posted by bananas View Post
    I am not allowed to use TeamViewer.
    If you've a computer at home, you could setup a similar sheet there and we could TeamViewer with you there.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  18. #38
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    With "," as decimal separator


    I think that 3,3 is a number. In the immediate window ?Vartype (3,3) = 5 or Double

    ?Vartype (3.3) is probably an error, but ?Vartype("3.3") = 8 or String


    So the String Criteria1:=">=0,2779" would work since Excel can convert that to a valid test for the number = 0,2779

    So Criteria1:=">=0.2779" would probably still work but not return anything since there is no string "0.2779" in the data to filter


    I don't think it would matter if you .Replace the "," by "."



    See #2 below

    http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm

    The Rules for Working with Excel

    1.Pass values to Excel in their natural format if possible (i.e. don't convert dates / numbers / Booleans to strings if you don't have to). If you have strings, convert them yourself before passing them to Excel.
    2.When you have to convert numbers and dates to strings for passing to Excel (such as in criteria for AutoFilter or .Formula strings),always explicitly convert the data to a US-formatted string, using Trim(Str(MyNumber)), or the sNumToUS() function shown earlier, for all number and date types. Excel will then use it correctly and convert it to the local number/date formats.
    3.Avoid using Date literals (e.g. #1/3/2001#) in your code. It is better to use the VBA DateSerial(), or the Excel DATE() functions, which are not ambiguous.
    4.If possible, use the date number instead of a string representation of a date. Numbers are much less prone to ambiguity (though not immune).
    5.When writing formulas in code to be put into a cell (using the .Formula property), create the string using English functions. Excel will translate them to the local Office language for you.
    6.When setting number formats or using the Format() function, use US formatting characters, e.g. ActiveCell.NumberFormat = "dd mmm yyyy". Excel will translate these to the local number format for you.
    7.When reading information from a worksheet, using .Formula, .NumberFormat etc, Excel will supply it using English formulas and US format codes, regardless of the local Excel language.

    Function sNumToUS(vValue As Variant, Optional bUseDATEFunction) As String
    ' *****************************************************
    ' *
    ' * Function Name: sNumToUS
    ' *
    ' * Input:    vValue - a variant containing the number to convert.
    ' *              Can be:
    ' *              a number  - to be converted to a string with US formats
    ' *              a date    - to be converted to a string in mm/dd/yyyy format
    ' *              a Boolean – converted to the strings "True" or "False"
    ' *
    ' *           bUseDATEFunction - an optional Boolean for handling dates
    ' *           False (or missing) - returns a date string in mm/dd/yyyy format
    ' *           True               - returns a date as DATE(yyyy,mm,dd)
    ' *
    ' * Output:   The input as a string in US regional format
    ' *
    ' * Purpose:  Explicitly converts an item to a string in US regional formats
    ' *
    ' *****************************************************
    
       Dim sTmp As String
    
       'Don't accept strings or arrays as input
       If TypeName(vValue) = "String" Then Exit Function
       If Right(TypeName(vValue), 2) = "()" Then Exit Function
    
       If IsMissing(bUseDATEFunction) Then bUseDATEFunction = False
    
       'Do we want it returned as Excel's DATE() function
       '(which we can't do with strings)?
       If bUseDATEFunction Then
    
          'We do, so build the Excel DATE() function string
          sTmp = "DATE(" & Year(vValue) & "," & Month(vValue) & "," & _
             Day(vValue) & ")"
       Else
          'Is it a date type?
          If TypeName(vValue) = "Date" Then
             sTmp = Format(vValue, "mm""/""dd""/""yyyy")
          Else
             'Convert number to string in US format and remove leading space
             sTmp = Trim(Str(vValue))
    
             'If we have fractions, we don't get a leading zero, so add one.
             If Left(sTmp, 1) = "." Then sTmp = "0" & sTmp
             If Left(sTmp, 2) = "-." Then sTmp = "-0" & Mid(sTmp, 2)
          End If
       End If
    
      'Return the US formatted string
       sNumToUS = sTmp
    End Function
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  19. #39
    VBAX Regular
    Joined
    May 2016
    Posts
    24
    Location
    p45cal, Paul,
    I think I solved it!
    Paul's last post got me to think in another way.
    I converted Double values to Integer values and it works.
    Code from: https://www.convertdatatypes.com/Con...n-VB6-VBA.html
    Here is my code:


    Sub StatTest()
    'Percent Values +-5%
    
    
    Dim wb1         As Workbook, wb2 As Workbook
    Dim ws1         As Worksheet, ws2 As Worksheet
    Dim rng         As range, rng9   As range
    Dim lr          As Long
    Dim MaxValue    As Double
    Dim MinValue    As Double
    Dim vInMax      As Double
    Dim vInMin      As Double
    Dim vOutMax     As Integer
    Dim vOutMin     As Integer
    
    
    Application.ScreenUpdating = False
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    Set wb2 = Workbooks("Book1.xlsm")
    Set ws2 = wb2.Sheets("Sheet1")
    lr = ws2.Cells(Rows.count, "A").End(xlUp).row
    Set rng = ws2.range("A1:J" & lr)
    Set rng9 = ws1.range("F132:F146")
    
    
    MaxValue = Application.WorksheetFunction.Max(rng9)
    MaxValue = MaxValue + 0.05
    MinValue = MaxValue - 0.1
    
    
    vInMax = MaxValue * 100
    vOutMax = CInt(vInMax)
    vInMin = MinValue * 100
    vOutMin = CInt(vInMin)
    
    
    ws2.AutoFilterMode = False
    rng.AutoFilter Field:=6, Criteria1:=">=" & vOutMin & "%", Operator:=xlAnd, Criteria2:="<=" & vOutMax & "%"
    
    
    Application.ScreenUpdating = True
    End Sub
    I have learned a lot last days so:
    Credit to both of you.
    If you see any faults with the code, please let me know. If not I will mark it as solved tomorrow.


    Thanks a lot!

Tags for this Thread

Posting Permissions

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