AutoFilter Percent Values +-5%
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!
Code:
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.