[nailgun198]

I have:
A worksheet with 11 columns, about 400 rows, with mostly text data.

I want:
To create a "Search" button on the spreadsheet that opens a form with a text box to enter search criteria, with a "Search" button and a "Cancel" button. When the user enters criteria and clicks "Search," rows with cells matching the data return in a different worksheet.

The problems:
Say the user wants to search for "Texas." Texas could be in several cells within the same rows - B7=Texas, C7=Texas Study, D7=Go Texas, E7=Texas, Texas, Texas, etc. So I don't want it to return the same row 6 times since it has 6 different instances of "Texas."

As shown above, the specific keyword they're looking for may be buried in other text within the same cell, so the search function needs to sort through and find all the instances.

I'm a beginner to this macro stuff, but I have the basics down. I just need help with a code for the search function. Any help would be appreciated!

[Viper]

Here's something easier...why not create a Pivot table of your data and then the user can perform their own filters? The double click on the data items returnd by the filter and a new sheet is created with those rows.

[nailgun198]

I'm even less familiar with Pivot Tables than I am with VBA. I've set up a Pivot Table but am having trouble figuring out how to tailor it so users can set up their own parameters. I see you can filter things, but I don't see where you can search and it will return the necessary records. I see there's a query function, but I'm not sure how to set it up, and we all know how helpful the "Help" button is. Can you offer me any more tips?

Thanks!

[bomb #21]

Welcome to the board.

"I have the basics down"

Which ones?

Assuming the 11 columns are A:K and row 1 is headers. Use a formula in a helper column (L), i.e.:

=IF(ISERR(SEARCH($M$1,A2&B2&C2&D2&E2&F2&G2&H2&I2&J2&K2)),0,1)

in L2 and copied down. Then you could use:

Sub test()
Range("M1") = Application.InputBox("What are you looking for?")
End Sub

and filter on M = 1. Or you could just enter the search term in M1 using the keyboard.

[nailgun198]

I tried the helper formula with the filter, which was a pretty genius idea, actually, and will help me in the future for my own personal endeavors. Thanks for your help.

However, it's not really "pretty enough" for the end users, and requires much too many instructions (I know, it's only like 4, but if it isn't easy they won't use it). I want them to be able to click a button, type what they want, click another button, and go to a different worksheet with all the records containing that keyword ready for them to see. Got any other ideas?

[bomb #21]

"not really "pretty enough" for the end users"

Gotta love "users".

"Got any other ideas?"

Tinker with the attached. HTH

Sub test()
If ActiveSheet.Name <> "Sheet1" Then Exit Sub
LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
Sheets("Sheet2").Range("2:1000").Delete
SearchTerm = Application.InputBox("What are you looking for?")
Application.ScreenUpdating = False
Range("L1") = SearchTerm
Range("L2:L" & LastRow).FormulaR1C1 = _
"=IF(ISERR(SEARCH(R1C12,RC[-11]&RC[-10]&RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]&RC[-3]&RC[-2]&RC[-1])),0,1)"
If WorksheetFunction.CountIf(Columns(12), 1) = 0 Then
Columns(12).Delete
Application.ScreenUpdating = True
MsgBox "None found."
Else
For Each Cell In Range("A2:A" & LastRow)
If Cell.Offset(, 11) = 1 Then
Cell.Resize(, 11).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
x = x + 1
End If
Next Cell
Columns(12).Delete
Application.ScreenUpdating = True
If x = 1 Then
MsgBox "1 matching record was copied to Sheet2."
Else
MsgBox x & " matching records were copied to Sheet2."
End If
End If
End Sub

[Viper]

yep, like the concatenation of text. ive used that method a lot.

[slurpee55]

If you used concatenation for each row (unlike this file, but you could end up with a column with cells very similar to what is in column B here), then you could use a formula like this one to find the word that was typed in.

[nailgun198]

this is pretty fabulous! And much more simple than anything I would have struggled to put together. I shined it up a bit by activating the search results worksheet (Sheets("SearchResults").Activate), but this is EXACTLY what I wanted.

I can't thank you enough for your help! Let me know if you'd like me to credit you in the code, I'd be happy to.

And thanks to the rest of your for your input!

[nailgun198]

Thank you!

[slurpee55]

Excellent, nailgun!
Please use the button at the top of the page to mark this thread as Solved.
And, belatedly, welcome to the forum!