VBA to find a value in multiple columns

[Neoptolemos]

I have an Excel file which has 9 columns, which all have some values in them, let's say A through G. The cells can also have multiple values in them (i.e. A, B, G).

I need some way to sort this file for all rows that have a particular letter in them, preferably using a textbox of some kind.

I've tried to do it with helper columns, but this gets really unwieldy because I need to create new columns for every value. The intended users of this file have even less Excel knowledge than I do, so preferably it should be very simple to operate.

I am using Excel 2007, but if this function is available in Excel 2010 I can upgrade.

 

[JackDanIce]

Welcome to the board

Try:

Sub FindMe()
Dim searchitem As String
Dim i As Long, j As Long
On Error Resume Next
searchitem = InputBox("Enter your search item")
If searchitem = "" Then Exit Sub
For i = 1 To 9
    For j = 1 To Cells(Rows.Count, i).End(xlUp).Row
        If InStr(Cells(j, i), searchitem) <> 0 Then Cells(j, 10) = "Exists"
    Next j
Next i
ActiveSheet.AutoFilterMode = False
With Range(Cells(1, 1), Cells(1, 10))
    .AutoFilter
    .AutoFilter field:=10, Criteria1:="Exists"
End With  
End Sub

 

[Neoptolemos]

Thanks JackDanIce, but I don't think that one quite works. I've uploaded this example to show you what I have right now. The example only includes 3 columns to be searched and values of A through C to keep things simple, but the textbox only applies to one column and I have no idea how to make it search columns D through F (OR, not AND).

 

[Neoptolemos]

I can't seem to edit my post anymore, but by saying "[doesn't] quite work" I meant that while it works perfectly, I would prefer the script to filter out those rows not matching the request rather than creating an 'exists column'. The reason for this is that the database will be rather large, and to have an 'exists column' at column Z or something is not very practical.

 

[Neoptolemos]

Bumpin' it up.

 

[Asator]

You could change the

Cells(j, 10) = "Exists"

to

cells(j, activesheet.usedrange.columns.count+1) = "Exists"

and

.autofilter field:=activesheet.usedrange.columns.count+1

That way it'd always put it in a new column at edge of your sheet. Food for thought.