Problem with filtering numeric fields by VBA Code

[mahhdy]

Hello,
I have a user form for filtering my desired inputs in all available tables in each sheet. It works perfect for text strings, but I can't do any filtering on the numeral fields! I am wondering what I am doing wrong! Thanks for your support.

Here is the form image and the code i have for filter:

Public Sub Filter()On Error Resume Next
Call Ref.appl_start
Dim sel As Range
        Dim e() As Variant
    nn = Application.Match(fltr.ComboBox2.Value, header, 0)
    If fltr.CheckBox1 = False And ActiveSheet.FilterMode = True Then Call loreset
    Select Case fltr.TextBox3.Value
    Case "Selection"
        ReDim e(Selection.Count - 1)
        If Selection.Columns.Count = 1 Then
            e = Application.Transpose(Selection)
        Else
            i = 1
            M = 0
            For Each Rng In Selection.Cells
                If Not Rng.Rows.Hidden And Rng.Value <> "" Then
                    e(M) = Selection(i).Value
                    M = M + 1
                Else
                End If
                i = i + 1
            Next
        End If
        fr.AutoFilter nn, e, xlFilterValues
    Case "Range"
        fr.AutoFilter nn, dict.Keys, xlFilterValues
    Case "Clipboard"
    Case "Input"
        If fltr.OptionButton1.Value Then
            fr.AutoFilter nn, "*" & fltr.TextBox2.Value & "*"
        Else
            fr.AutoFilter nn, fltr.TextBox2.Value
            End If
    End Select


Call Ref.appl_End
End Sub

 

[wideboydixon]

I can't easily replicate your sheet and code but I would suggest that for numeric fields, you'll need to convert TextBox2.Value to a number as it's currently a string. CLng() or CDbl() could help you here. Of course, you'd need to know which columns are numbers ...

WBD

 

[mahhdy]

Hello,

Actually it is an Add-in I am developing for my own use. I can share it. But my problem is with selection cells which have numeral values and the filter ranges also are value at same time. So the data matches, my code can not let excel understand and match the cell type. I was wondering if I have to translate everything for that or not... If so I need much more efforts!
anyway here is the add-in.
https://1drv.ms/x/s!ArGi1KRQ5iItgawugfTDT8vB6NxoCg

I think the problem is in the way excel filters different data types and I am not aware of that!

I can't easily replicate your sheet and code but I would suggest that for numeric fields, you'll need to convert TextBox2.Value to a number as it's currently a string. CLng() or CDbl() could help you here. Of course, you'd need to know which columns are numbers ...

WBD

 

[wideboydixon]

You may need to "guess" based on the contents of the first row in the column. For example:

    Case "Input"
        If fltr.OptionButton1.Value Then
            fr.AutoFilter nn, "*" & fltr.TextBox2.Value & "*"
        Else
            If CellType(fr(nn)) = "Value" Then
                fr.AutoFilter nn, CDbl(fltr.TextBox2.Value)
            Else
                fr.AutoFilter nn, fltr.TextBox2.Value
            End If
        End If

WBD

 

[mahhdy]

Yes I fixed that in other way, actually for exact matches it was working even before. My actual question is for the cases I am taking the criteria from the sheet, for more then one criteria. For instance I am selecting 5 cells with numerical data. I am putting them to an array, but filter is not returning anything for that array. So I am not talking about single entry by that textbox. My problem is multiple numeral selections...

    Case "Input"        If IsNumeric(fltr.TextBox2.Value) Or fltr.OptionButton2.Value Then
            fr.AutoFilter nn, fltr.TextBox2.Value
        Else
            fr.AutoFilter nn, "*" & fltr.TextBox2.Value & "*"
        End If
    End Select

or

    Case "Input"
        If IsNumeric(fltr.TextBox2.Value) Then
            fr.AutoFilter nn, CDbl(fltr.TextBox2.Value)
        Else
            If fltr.OptionButton1.Value Then
                fr.AutoFilter nn, "*" & fltr.TextBox2.Value & "*"
            Else
                fr.AutoFilter nn, fltr.TextBox2.Value
            End If
        End If
    End Select

You may need to "guess" based on the contents of the first row in the column. For example:

    Case "Input"
        If fltr.OptionButton1.Value Then
            fr.AutoFilter nn, "*" & fltr.TextBox2.Value & "*"
        Else
            If CellType(fr(nn)) = "Value" Then
                fr.AutoFilter nn, CDbl(fltr.TextBox2.Value)
            Else
                fr.AutoFilter nn, fltr.TextBox2.Value
            End If
        End If