Hi, Kevin.

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).

Unfortunately, Excel doesn't have wildcard searches the way Word does. I
tried to do something using a wildcard search and wound up looking at two
directions:
(a) open an instance of Word, copy your Excel text or range into it, and
use Word's search capabilities. I got this to work, but I had Word flashing
off and on and it took quite a bit of time.
or
(b) try to instance Regular Expressions, which has tremendous wildcard
capabilities. This should be do-able, but I haven't explored it much at
all.

What you might want to consider is searching for "po" using Excel's
Range.Find with LookAt:= xlPart. I don't know that you can constrain that
to "poetry" and not "impotent" without either comparing to a list or using
some string functions to check the first letters. (That last part wouldn't
be hard - you'd just use your search string as the compare string and reject
the return if it didn't match.)

HTH
Ed

wrote in message
oups.com...
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"