• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Filter & show different values from any record

Dear experts,

I'm trying to figure a way how to include records that are ''different''.

A sample is included below.

A new button was added, filter by rccd & acctopid.
After a search, some records need to be shown. If I enter 13, it will show only those that belong to this number, but how to make it show the ones that also belong to this number, but that are like 13 C ?

Thanks
currentdb
userform-sample-v7b-6.xlsm
0
currentdb
Asked:
currentdb
  • 8
  • 8
1 Solution
 
SiddharthRoutCommented:
Looking into it :)

Sid
0
 
currentdbAuthor Commented:
Good :)
0
 
SiddharthRoutCommented:
Try this

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    
    On Error GoTo Whoa
    
    lastRow = Sheets("QUERY_FOR_GSL2").Range("A" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(TextBox1.Text)) = 0 Or Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The textbox cannot be empty"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2").Range("$A$1:$BC$" & lastRow)
        .AutoFilter Field:=1, Criteria1:=TextBox1.Text
        .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
        Operator:=xlOr, Criteria2:="=" & TextBox2.Text & "*"
        Unload Me
    End With
    Sheets("QUERY_FOR_GSL2").Activate
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
currentdbAuthor Commented:
Wow! That was fast! :)

And it works as expected. Just made another test. If inserting the value 13 C, would that return both 13 and 13 C values ?

If I insert only 13, it will return 13 and 13 C, but if doing the inverse thing, it will only return 13 C. How I can make it return 13 C and 13 ?
0
 
SiddharthRoutCommented:
AFAIK, the reverse is not possible :( I could be wrong though.

Sid
0
 
currentdbAuthor Commented:
If the reverse is not possible, then we will leave it as it is. I just checked and there are 5 spaces...13 space 5 times then C. LOL

I will add a note as it's better to insert 12 or 13 for that matter and it will show relevant records plus the ones with the ''C''. It's just easier as it is.
0
 
SiddharthRoutCommented:
>>>I will add a note as it's better to insert 12 or 13 for that matter and it will show relevant records plus the ones with the ''C''. It's just easier as it is.

That makes sense :)

Sid
0
 
SiddharthRoutCommented:
Wait... I think there is an alternative. Let me give it a shot.

Sid
0
 
currentdbAuthor Commented:
Thanks again for your fast help, the fastest one amongst all ee experts! :)
0
 
SiddharthRoutCommented:
Seems you missed my post ID: 35193102 ;)

Try this :)

Private Sub CommandButton1_Click()
    Dim lastRow As Long
    Dim findText As String, MyArray() As String
    
    On Error GoTo Whoa
    
    lastRow = Sheets("QUERY_FOR_GSL2").Range("A" & Rows.Count).End(xlUp).Row
    
    If Len(Trim(TextBox1.Text)) = 0 Or Len(Trim(TextBox2.Text)) = 0 Then
        MsgBox "The textbox cannot be empty"
        Exit Sub
    End If
    
    With Sheets("QUERY_FOR_GSL2").Range("$A$1:$BC$" & lastRow)
        .AutoFilter Field:=1, Criteria1:=TextBox1.Text
        
        If InStr(1, TextBox2.Text, " ") Then
            MyArray = Split(TextBox2.Text, " ")
            findText = Trim(MyArray(0))
            .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
            Operator:=xlOr, Criteria2:="=" & findText
        Else
            .AutoFilter Field:=15, Criteria1:="=" & TextBox2.Text, _
            Operator:=xlOr, Criteria2:="=" & TextBox2.Text & "*"
        End If
        Unload Me
    End With
    Sheets("QUERY_FOR_GSL2").Activate
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Open in new window

0
 
currentdbAuthor Commented:
Sure no problem. I closed the question too fast, but I still receive notifications from my knowledge base. So you can try a shot while you have time of course :)
0
 
SiddharthRoutCommented:
>>>>Sure no problem. I closed the question too fast, but I still receive notifications from my knowledge base. So you can try a shot while you have time of course :)

Already done that ;)

See above.

Sid
0
 
currentdbAuthor Commented:
Ok lemme give it a try now :)
0
 
currentdbAuthor Commented:
Looks like it works, so the reverse thing was not impossible after all :)

Thanks you again for your time and a very great help through this.
0
 
SiddharthRoutCommented:
No it is not impossible if you use a workaround like I did :)

Sid
0
 
currentdbAuthor Commented:
Of course you are right and it was a very good workaround after all :)

Thanks again :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now