Search code examples
excelvbafiltercriteriainputbox

Using Inputbox for Filter Criteria in VBA

This may be lame but i need help in VBA while using Input Box for Filter Criteria. I'm trying to use below code where the user needs to put values in + & - to filter the range accordingly.

In this case i'm trying to filter 5 for upper range and -4.5 for lower range. The difficulty i am facing is that when i type -4.9, -4.8, -4.7 or -4.6 for lower range then the value is considered as -5 and when i use -4.5, -4.4, -4.3 and so on till -4.1 then the value is considered as -4. I need the value to be exact as i input in the input box.

Sub InputFilter()

    Dim iuval As Integer
    Dim ilval As Integer

    iuval = InputBox("Please enter the upper range")
    ilval = InputBox("Please enter the Lower range")


    Range("G1").Select
    ActiveSheet.Range("$A$1:$H$71").AutoFilter Field:=7, Criteria1:=">" & iuval, _
        Operator:=xlOr, Criteria2:="<" & ilval

End Sub
Solution

  • Change the data type of iuval to double otherwise VBA will convert your input automatically to integer which means in your case it will convert your input to -4 or -5.

    Dim iuval As Integer should be Dim iuval As double

    Do the same for Dim ilval As Integer.