AutoFilter Percent Values +-5%

[bananas]

Hello,
Code works fine in other Fields(no percentage) but not in Field:=6.
rng9.NumberFormat = "0%"
Field:=6: Numberformat = "0%". No blanks.
lr >50000 rows.
MaxValue rng9 shows 33%, formula field shows 32,79%.

Step by step:
Code line: MaxValue = Application.WorksheetFunction.Max(rng9) shows 0,3279
Code line: MaxValue = MaxValue + "0,05" shows 0,3779
Code line: MinValue = MaxValue - "0,1" shows 0,2779
DropDown menue Field:=6 shows all values in Numberformat "0%"

Problem is:
Code line: rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue ,makes sheet empty except first row.
DropDown menue Field:=6 shows "Between" values 0,2779 and 0,3779.
One odd thing is: If I click "OK" button in DropDown menue Field:=6>"Number Filters">"Between", AutoFilter filter as expected!

Sub StatTest()
'Percent Values +-5%
Dim lr          As Long
Dim MaxValue    As Double
Dim MinValue    As Double


lr = ws2.Cells(Rows.count, "A").End(xlUp).row
Set rng = ws2.range("A2:AJ" & lr)
Set rng9 = ws1.range("F132:F146")
rng9.NumberFormat = "0%"
MaxValue = Application.WorksheetFunction.Max(rng9)
MaxValue = MaxValue + "0,05"
MinValue = MaxValue - "0,1"


With ws2
    .AutoFilterMode = False
    .range("A1:AJ1").AutoFilter
    rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
End With


End Sub

Can anyone tell me what I'm doing wrong?
Any help will be greatly appreciated.

[p45cal]

I suspect these two:

MaxValue = MaxValue + "0,05"
MinValue = MaxValue - "0,1"

need to be

MaxValue = MaxValue + 0.05
MinValue = MaxValue - 0.1

[bananas]

Hi p45cal,
Thanks for quik reply.
When I remove the quotation marks code line goes red.

[p45cal]

change the commas to full stops too

[bananas]

I get the Max- and Min- values but still same result as before.

[p45cal]

I tested it here as far as I could; it hinges on what's in ws2.range("A2:AJ" & lr) and ws1.range("F132:F146") and whether they're comparable.
Attach a worksheet, desensitised; you can remove a lot but keeps the values in those ranges and make sure the code which sets ws1 and ws2 is present. Make sure the file still has the problem after you desensitise it.

[bananas]

Attached workbook still have same problem.

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 = ActiveWorkbook    'For me:"Book2.xlsm"
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("A2:AJ" & lr)
Set rng = ws2.range("A2:J" & lr)            'Made used range smaller for easier overview. Still same problem.
Set rng9 = ws1.range("F132:F146")
rng9.NumberFormat = "0%"                    'To show and make sure rng9 and Col.F have same format.
ws2.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 ws2
    .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

[p45cal]

So there are two workbooks and you only supply one…

1. Supply both workbooks.
2. Include the vba code in the workbook, ready to run without me having to cut and paste it (where? I don't know), not separately.
3. Tell me which workbook is the Active workbook at the time the code starts running.

[bananas]

Please read text at "Book2", ws1.range("A1:A4").

[p45cal]

Are you sure Book2.xlsm is the correct file; it seems unrelated to me.

[p45cal]

I copied cells O10:O24 to cells F132:F146 (why couldn't you have done that?)
I pasted the code in msg#7 into a code-module of Book2.xlsm (I'm only guessing that's where it belongs - you should have done that.).

Initially maxValue was 0.3279, later it became 0.3779 while MinValue became 0.2779.
When that macro stopped running I got:
2020-09-02_164130.jpg
Was that not what you wanted?

Because of the line:
Set wb1 = ActiveWorkbook
the result very much depends on which workbook is the active workbook while that line is executed.

[bananas]

If you have below text in range("A1:A4") (Book2.xlsm) and percentage values in range("F132:F142"), I'm 100% sure.
A1: This is the active workbook(wb1).
A2: Scroll down to row 132 (in this workbook) and there you have the values in Col. F.
A3: Make sure you have wb2 ("Book1") open but this workbook must be active.
A4: I have included the code in this workbook but I normally keep my codes in the "Personal" workbook.
Book2.xlsm only have one sheet ("Sheet1") with this data plus code.

[bananas]

That is exactly what I want but I get a blank page except first row.
I only get your result if I click "OK" button in DropDown menue Field:=6>"Number Filters">"Between". Max-value 0.3779 and MinValue 0.2779 is already there.
Strange it works for you but not for me. If I don'y have percent values it works.

[p45cal]

If you have below text in range("A1:A4") (Book2.xlsm) and percentage values in range("F132:F142"), I'm 100% sure.
A1: This is the active workbook(wb1).
A2: Scroll down to row 132 (in this workbook) and there you have the values in Col. F.
A3: Make sure you have wb2 ("Book1") open but this workbook must be active.
A4: I have included the code in this workbook but I normally keep my codes in the "Personal" workbook.
Book2.xlsm only have one sheet ("Sheet1") with this data plus code.

No, that text is NOT in any sheet.

[bananas]

Are you sure your "Book2" comes from me?
Update: I downloaded "Book2" and I can see now that the "Book2" doesn't come from me. How is that possible?
I have no idea what that workbook is.

[p45cal]

Are you sure your "Book2" comes from me?

2020-09-02_171347.jpg


Yours?

[bananas]

No, that is not my workbook "Book2" or any of my thousands of workbooks.
This is a security risk!

[p45cal]

OK, well try attaching Book2 again, perhaps giving it a less common name? I don't know the answer to your question but at least I'll be able to see/trial the actual code used in a module therein and know exactly what's in F132:F142.
Your locale is Sweden right?
What version of Excel?

[p45cal]

No, that is not my workbook "Book2" or any of my thousands of workbooks.
This is a security risk!

In a thread started by you here: http://www.vbaexpress.com/forum/show...ste&highlight=
there is an attachment called Book2, it's the same as the attachment Book2 in this thread, and it has a very Swedish sounding name as the author:Torbjörn Strömberg !!

[bananas]

Yes, Sweden it is.
I'm 100% sure that workbook doesn't come from me.
Excel 2019