Using Inputbox for Filter Criteria in VBA

Question

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

Storax · Accepted Answer · 2020-05-03 11:34:33Z

2

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.

answered May 3, 2020 at 11:34

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Olivier Depriester Over a year ago

And actually as InputBox returns a string and these values are then only concatenated , you could even define them as String. The only point of using numeric datatypes here would be to ensure the user input a numeric value. But you have the IsNumeric function for this.

Storax Over a year ago

I am not sure if this is the better approach because we have a string comparision in Criteria1:=">" & iuval then which might give wrong results. But you are right to be on the safe side on one should use IsNumeric before convertting.

Olivier Depriester Over a year ago

Whatever is the type of iuval, Criteria1 will be a string (ie : ">-4.5"). Then it is the Excel engine that parses and interprets this string as a "greater than numeric value -4.5".

Storax Over a year ago

According to the documentation criteria is of datatype variant. But, of course, in this case it is a string and Excel will do the parsing.

Olivier Depriester Over a year ago

Even if Criteria1 can accept any datatype (that is the meaning of typing it as Variant), ">" & iuval returns a String as you concatenate a String (">") with anything else. So the value of the argument Criteria1 in this context will always be a String

|

Collectives™ on Stack Overflow

Using Inputbox for Filter Criteria in VBA

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related