Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Writing A Search Macro With Wildcard Characters?
If anyone has enough time to help me with this matter, it would be
greatly appreciated. Here is some needed set-up info. and what I am trying to accomplish. I have a list in the form of an array(actually its a multi-column array, but only one column is being utilized in this particular macro). On a sheet is a textbox and a command button. What I would like to accomplish is that when a user types an entry in the textbox and clicks the "Search" command button, the attached macro will loop through the list returning one of two results. A) It runs a loop through the list and if an exact match is found, the found match and coinciding info(found in the other columns of the array) are written to specific cells to later be displayed on a userform that will pop up after the former code is exhausted. B)If an exact match isn't found on the first loop, a second loop is run through the same list and populates a combobox on another userform with the next closest matches for the user to pick from. (In essence if the user was searching for "poetry" and the 1st. loop doesn't find it, the second loop will look for all other instances comprised of "po*") I have completed A) but am pulling my hair out with B)...the code for A) looks like this.... Private Sub ASSearch_Click() Dim r1 As Integer 'loop through skills on ctrl sheet to find skill being searched r1 = 0 Do r1 = r1 + 1 'if skill being searched is found, paint skill name, number of ranks, and bonus to ctrl sheet If Sheet4.Range("CtrlSkills").Cells(r1, 2) = ASSkillSearchBox.Value Then Sheet4.Range("CtrlSkillID") = Sheet4.Range("CtrlSkills").Cells(r1, 2) Sheet4.Range("CtrlRanks") = Sheet4.Range("CtrlSkills").Cells(r1, 3) Sheet4.Range("CtrlBonus") = Sheet4.Range("CtrlSkills").Cells(r1, 8) End If Loop Until Sheet4.Range("CtrlSkills").Cells(r1, 2) = "" 'loop through skill again, stopping at skill being searched for If Sheet4.Range("CtrlSkillID") = ASSkillSearchBox.Value Then r1 = 0 Do r1 = r1 + 1 Loop Until Sheet4.Range("CtrlSkills").Cells(r1, 2) = ASSkillSearchBox.Value 'from found skill loop up and stop at catagory name Do r1 = r1 - 1 Loop Until Sheet4.Range("CtrlSkills").Cells(r1, 1) = "c" 'paint catagory name to ctrl sheet Sheet4.Range("CtrlCatagoryName") = Sheet4.Range("CtrlSkills").Cells(r1, 2) 'return skill information in skill return userform ASSkillReturn.Show Else 'open closest matches userform ASSkillMatch.Show End If End Sub I am an amateur vba user and self taught...I am six months into this program and learning a lot but this seems to be a "code" I can't crack. Is what I'm trying to do possible and if so can anybody give me advice on how to accomplish it. Thank You Kevin a.k.a. "Torahn" |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Use of Wildcard characters with replace | Excel Discussion (Misc queries) | |||
can wildcard characters be used in IF functions | Excel Worksheet Functions | |||
wildcard characters | Excel Discussion (Misc queries) | |||
HELP: Wildcard Characters | Excel Discussion (Misc queries) | |||
WildCard Characters | Excel Worksheet Functions |