0

With this VBA macro for Excel I get instant search filter when I type the whole word, but I want it as follows: when I type O for opel, that I get opel already showing.

This is the code:

Sub MG26Nov26
Private Sub TextBox1_Change()
Dim Ray As Variant
If Len(TextBox1.Value) = 0 Then
    Sheet1.AutoFilterMode = False
    Else
        If Sheet1.AutoFilterMode = True Then
            Sheet1.AutoFilterMode = False
        End If
    Ray = Split(TextBox1, "+")
    Sheet1.Range("A2:F" & Rows.Count).AutoFilter field:=1, Criteria1:=Ray, Operator:=xlFilterValues  
    End If

End Sub

This is the test file:

https://drive.google.com/open?id=1KDwE2jT_u35wPhd5dR7Xi3X31ddzXDNx

  • Try changing ray to Ray = Split(TextBox1 & "*", "+") – Christofer Weber Nov 28 '18 at 0:06
  • This worked for me too. You have really excel skills! thank you very much – Louai Al Falahi Nov 28 '18 at 16:07
0

To solve the issue, you can use the below written VBA code.

Private Sub TextBox1_Change()
Dim Ray As Variant
If Len(TextBox1.Value) = 0 Then
    Sheet1.AutoFilterMode = False
    Else
        If Sheet1.AutoFilterMode = True Then
            Sheet1.AutoFilterMode = False
        End If

    Sheet1.Range("A2:F" & Rows.Count).AutoFilter field:=1, Criteria1:="*" & TextBox1.Text & "*", Operator:=xlFilterValues
    End If

End Sub

N.B. This is the Change I've done in the VBA Code Criteria1:="*" & TextBox1.Text & "*" in place of the Ray variable.

  • Woowww that worked for me.Thank your very much!! – Louai Al Falahi Nov 28 '18 at 16:06
  • @LouaiAlFalahi,, glad to help you and happy to learn that my solution worked for you. If you wish accept it as Answer also you can up vote it ☺ – Rajesh S Nov 29 '18 at 6:55

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.