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:
27050
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?
27052


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/showthread.php?56310-Dynamically-Copy-and-Paste&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 !!:rotlaugh:

[bananas]

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

[p45cal]

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]

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]

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.

[snb]

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

[p45cal]

snb, if that works then - 0.1 would need to be - 0.05

[bananas]

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.

[bananas]

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!

[bananas]

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!

[snb]

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

[p45cal]

If you could arrange for us to have a TeamViewer session (or similar) I think it would be faster.

[Paul_Hossler]

FWIW, it seems to work OK here



27075


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

[bananas]

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.

[p45cal]

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]

What do you get if in the Immediate pane you type:
?">=" & 3.3and 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))

[Paul_Hossler]

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

[bananas]

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.

[p45cal]

Then I'm at a loss.

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.

[Paul_Hossler]

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/Ch22/ProgRefCh22.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

[bananas]

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/Convert-Double-to-Integer-in-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!