Search by Topic or Purpose

Experts,
Next to the command button in cell A1, I need a search function with two choices -  Find by Topic or Find by Purpose.
See attached workbook
Thanks
Excel-Formulas-and-Functions-Rev.xlsm
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
After the results are found, how would you like them displayed? In a new listbox or would you like the rows below row 9 filtered to show only the ones that match the search criteria?
0
Frank FreeseAuthor Commented:
A new list box please
0
Martin LissOlder than dirtCommented:
OK, I have to go out shortly for the est of the afternoon but I should have something for you tonight or tomorrow morning.
0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Frank FreeseAuthor Commented:
No problem and many thanks
0
Martin LissOlder than dirtCommented:
As promised here's a solution. I added a 'Search' button next to the 'Exit Excel' button. When that button is clicked this userform appears.
frmSearch
The 'Look In' checkboxes tell the code where to look for the search terms. If 'Formula or Function Topic' checkbox is checked the search will be done against column B on the 'Menu' sheet and if the 'Purpose' checkbox is checked the search will be done against column C. If both are check, both columns are searched. (A small note and that is that the checkbox names come directly from B9 and C9 so if you change those cells the checkboxes will change.)

The two textboxes in the 'Search Terms' frame are where you enter what you want to find and you can use either one or both. If both are used then both terms must be found.

When you click the 'Find' button, the large box (a listbox) below the search terms will be filled with all the matches. The text of those matches will always be the text from column B even if you are searching just column C. When you click one of the items in the list you will be taken to the appropriate detail sheet.

The changes are annotated with "Q4". It's possible that I made some changes outside of the new userform and forgot to annotate them. If that happened I apologize.

The userform's code is pretty simple now. At first I wasted a lot of time trying to use Excel's Find command to do the searching but with the options I provided for the search and having to do what was basically a "Find All", the code became pretty complex, and when I was tackling the last task which was the code to do the find all with two search terms in both columns B and C, I struggled with it unsuccessfully until I realized that you don't have a lot of rows so I could use a simple row by row text search not only for that but for all the other (complex) search code I had previously written so I threw out that code and restarted from scratch. (Phew, that's quite a sentence:) )
Q-28240058.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
You da man!
What a fantastic solution.
I've got a lot of maintenance to do now and many more Categories and Topics to add so being the nice guy I am, I'll give you a break :)
Martin, truly, many sincere thanks. I'm looking forward to studying the coding here so I can learn.
You simply nailed it!
0
Frank FreeseAuthor Commented:
This is a good, hard working EE associate and he has done a fantastic job in working with me. People like him is why I see EE as being the "best" of the boards out there. Simply the very best
0
Martin LissOlder than dirtCommented:
Thank you for the kind words.

Marty - MVP 2009 to 2013
0
Frank FreeseAuthor Commented:
My pleasure - talk with you later
0
Frank FreeseAuthor Commented:
Martin,
I'm getting an error, though not related to the Search routine, but instead, when I'm modifying a Category and the Topic description. See attached word document and new workbook.
Error.doc
Function-and-Formulas-for-Excel.xlsm
0
Martin LissOlder than dirtCommented:
Going out for some exercise and I'll take a look when I get back.
0
Frank FreeseAuthor Commented:
No problem
0
Martin LissOlder than dirtCommented:
I could not reproduce the problem but I found what is probably causing it. In your changes to the Topics sheet you somehow introduced blanks and/or deleted some of the cells in some of the columns that are used for the named ranges of the categories. Those columns are A, C, E, G, I and K and they should never have any blank cells. I thought I could fix the problem by merely selecting the blank range in the middle of those columns and delete them using the 'Move cells up' option. When I did that I found that the text in the cells did not match up with the text on the Menu rows. You can perform the same test that I did and that is to select a category and then the first and then the last item in the topics for that item. For example when Formulas is selected. clicking the 1st item in topics highlight row 10 and the last topic highlights row 43, both of which are correct, but doing the same for Logical Functions when you click the last item it highlights row 56 rather than 60. When I reopened the workbook without saving the changes, things were still off and that's why I say that you may have both deleted the contents of some cells and/or added some blank cells.

The blank cells are pictured here in C4, 5 and 6, E4, 5 and 6, etc.
Blanks
0
Frank FreeseAuthor Commented:
Well "operator error" is my middle name - thanks for the follow-up. I'll be more careful
0
Martin LissOlder than dirtCommented:
There apparently more items missing from Topics then I thought. Add this code to modVisible. What it does is to compare Menu sheet column B line by line with the Topics sheet and if it doesn't find a match it prints the text from column B. To use it, go to the Immediate Window, type in "FindMissingTopics" (without the quotes) and press return.

Public Sub FindMissingTopics()
Dim lngRow As Long
Dim FindIt As Range
Dim intMissing As Integer

Debug.Print "======= The following are missing from the Topics sheet ======="
With Sheets("Menu")
    For lngRow = 10 To Sheets("Menu").Range("B65536").End(xlUp).Row
        Set FindIt = Sheets("Topics").Columns("A:K").Find(What:=.Cells(lngRow, 2).Value, After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If FindIt Is Nothing Then
            Debug.Print .Cells(lngRow, 2).Value
            intMissing = intMissing + 1
        End If
    Next
End With

If intMissing = 0 Then
    Debug.Print "Nothing missing"
End If
End Sub

Open in new window

0
Frank FreeseAuthor Commented:
Hey, that's cool
Thanks for the follow-up
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.