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)
Printable View
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)
On the other hand, don't.Quote:
Originally Posted by p45cal
In your code:
.Range("A1:AJ1") is the header row, but rng is the data body excluding the headers.Code:.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:Code:Set rng = ws2.Range("A2:J" & lr)
and later, change:Code:Set rng = ws2.Range("A1:J" & lr)
to just:Code:With ws2
.AutoFilterMode = False
.Range("A1:AJ1").AutoFilter
rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
End With
Code:ws2.AutoFilterMode = False
rng.AutoFilter Field:=6, Criteria1:=">=" & MinValue, Operator:=xlAnd, Criteria2:="<=" & MaxValue
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.
Seems to be sufficient:
Code: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, 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!
Code: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
Quote:
Originally Posted by p45cal
FWIW, it seems to work OK here
Attachment 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
Code: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
Code: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
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 ",".Code: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).Code: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
What do you get if in the Immediate pane you type:and press Enter?Code:?">=" & 3.3
>=3.3
or
>=3,3
?
To make my last suggestion work in more locales consider also:
Code:crit1 = Replace(">=" & MinValue, ".", Application.International(xlDecimalSeparator))
crit2 = Replace("<=" & MaxValue, ".", Application.International(xlDecimalSeparator))
xlsx or xlsm doesn't make any difference. Still works just fine without having to hit OK.Quote:
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
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!Code: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.
Then I'm at a loss.
If you've a computer at home, you could setup a similar sheet there and we could TeamViewer with you there.Quote:
Originally Posted by bananas
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
Quote:
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.
Code: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
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: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
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!