Search and filter based on InputBox entry

Question

I'm hoping to achieve the following:

Take user input via the Input box.
Search the table headers for that text.
Filter the found column to remove all blank cells (Leaving just the cells with data in.)

I've progressed a bit with a script I found, to give the input box, search the table header and select the found cell.

I need to merge into this the step of filtering the column of the found cell. If I record the steps it filters the same column no matter what I search for, so I think I need a way of reading back the found cell details and choosing that column to filter out blanks.

Sub Find_First()

    Dim FindString As String
    Dim Rng As Range
    FindString = Application.InputBox("Enter a Search value")
    If Trim(FindString) <> "" Then
        With Sheets("ACM").Range("B2:DA2") ' This is the table headers
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            Else
                MsgBox "Nothing found"
            End If
        End With
    End If
End Sub

I now have it working using the following code, the only error I now get is a 1004 (WorksheetFunction class) error if I cancel the InputBox :-

Sub Find_First()
    Dim i1 As Integer
    Dim FindString As String

    Dim Rng As Range
    Dim rngData As Range
    Set rngData = Application.Range("A2").CurrentRegion
        FindString = Application.InputBox("Enter a Search value")
        If Trim(FindString) <> "" Then
            With Sheets("ACM").Range("B2:DA2") ' This is the table headers
                Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                If Not Rng Is Nothing Then
                    Application.Goto Rng, True
                Else
                    MsgBox "Nothing found"
                End If
            End With
        End If

    i1 = Application.WorksheetFunction.Match(FindString, Application.Range("A2:CZ2"), 0)
    Rng.AutoFilter Field:=i1, Criteria1:="<>"

End Sub

You're not going to find a sample of code on the internet that does everything for you. You may be able to get a good answer by starting with your first step and explaining what issues you are specifically having. Are you able to make the input box and set its input to a variable? Do you know how to search a row for your variable? What if your input is spelled incorrectly by the user? Do you know how to automate the autofilter? — Brent, Aug 17 '18 at 15:10
If you're having trouble patching that code together, show us the code you've tried — Marcucciboy2, Aug 17 '18 at 15:13
Do you have the input box working? Or any failing attempt at it? Please read [*Why is “Can someone help me?” not an actual question?*](https://meta.stackoverflow.com/q/284236/1188513) — Mathieu Guindon, Aug 17 '18 at 15:14
That looks like a pretty good start, the only part I would change for part 2 is `xlWhole`to `xlPart` so that you can search for just a word in the header and not the whole header. Really you're just left with how to complete part 3 — Marcucciboy2, Aug 17 '18 at 15:59
You can check this link out to see how you could set your filter. In their example they use `ActiveCell` but yours would be `Rng` https://www.mrexcel.com/forum/excel-questions/754447-vba-filter-out-blanks-selected-column-macro.html — Marcucciboy2, Aug 17 '18 at 16:02
Thanks for this. I've adjusted it to xlPart to try. I've tried adding the filter as part of the if statement (Also tried it at the end), but I get a run-time error 1004 - AutoFilter method of Range class failed. This is the part I added it to :- — James Thompson, Aug 17 '18 at 16:40
If Not Rng Is Nothing Then Application.Goto Rng, True Rng.CurrentRegion.AutoFilter Rng.CurrentRegion.Column - Rng.Column + 1, "<>" — James Thompson, Aug 17 '18 at 16:40

score 0 · Answer 1 · answered Aug 17 '18 at 16:18

0

Looks like you really need the autofilter worked out:

I've done this in a similar scenario:

Dim i1 as Interger
Dim rngData as Range
Set rngData = ws.Range("A1").CurrentRegion

Using Match to find my column number matching FindString

i1 = Application.WorksheetFunction.Match(FindString, ws.Range("A1:CZ1"), 0)
rngData.AutoFilter Field:=i1, Criteria1:="<>"

answered Aug 17 '18 at 16:18

Brent

98
9

Thanks Brent, would I replace my existing find with this ? It looks like this scans a range for the input data and filters in one go. – James Thompson Aug 17 '18 at 16:52
Yes, customize you range for where you want to look. In this case, I was looking at my header row for an exact match of the full header name. It finds the column where the text matches and then filters that column to include any non-blank items. This assumes your column headers started on A1. It is defining the column as the variable i1 and then using that to decide which column to filter. If users may look for 1 item & then do another search, but it shouldn't be combined in 1, then you may want a 'ws.ShowAllData' prior to the filter to prevent filtering out desirable results. – Brent Aug 17 '18 at 17:41
At first it errored when using ws.Range when setting the rngData. I changed it to Application.Range which it seems to be happy with. I also updated that in the search and filter. It now errors finding the WorksheetFunction class (error 1004) which I can't find a way to solve. – James Thompson Aug 20 '18 at 09:40
Hi Brent, I have it working now by combining your script with the original I posted (Thanks very much). So now it works to find and filter the column. I still get the error about the worksheetfunction class (1004) when I cancel the InputBox so something still isn't quite right. I'll update the main thread with hon it looks now as I can't paste the code in these small comment boxes. – James Thompson Aug 20 '18 at 10:35
the ws. reference in my code was how i called my worksheet. change it to whatever variable you are setting for the worksheet to ensure it always goes to the correct sheet. – Brent Aug 20 '18 at 13:26
Also, you may try this: https://stackoverflow.com/questions/16061562/trouble-with-inputboxes for how to deal with your input box cancel. – Brent Aug 20 '18 at 13:32

Search and filter based on InputBox entry

1 Answers1