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)
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.
On the other hand, don't.
In your code:
.Range("A1:AJ1") is the header row, but rng is the data body excluding the headers..Range("A1:AJ1").AutoFilter rng.AutoFilter Field:=6, Criteria1:=">="…
Normally, I'd expect the two .Autofilter lines to act on the same range. So for consistency, I'd have
changed to:Set rng = ws2.Range("A2:J" & lr)
and later, change:Set rng = ws2.Range("A1:J" & lr)
to just:With ws2 .AutoFilterMode = False .Range("A1:AJ1").AutoFilter rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue End With
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.
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.
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
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.
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.
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!
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!
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
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
---------------------------------------------------------------------------------------------------------------------
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
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!
One observation: Max value show value with ",".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
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.
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
It should NOT matter what format the numbers are in (neither rng9 nor column F, they won't need to be similar formats either).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
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.
What do you get if in the Immediate pane you type:and press Enter??">=" & 3.3
>=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.
xlsx or xlsm doesn't make any difference. Still works just fine without having to hit OK.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"
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
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:
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!ws2.AutoFilterMode = False rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
I also tried code on another computer with Excel 2016 but same result.
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
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:
I have learned a lot last days so: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
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!