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"

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"

Thanks very much for the help Ed. I think I would like to explore your
advice on using range.find...I'm just not sure how to go about doing
that. Like I said, I am still in the learning process and that is one
area of VBA that I haven't explored. Unfortunately, I don't find the
help files found in excel and vba very helpful.
Could anyone please try to advise me on using range.find to accomplish
what I'm trying to accomplish?
Here is what I would like to do(the simplified version). I have a
textbox and a command button on a worksheet. When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches. So if the user
typed in poetry, and accidently spelled it wrong, when they click the
"search" command button it will check the list where the entry is
supposed to be contained and find one or more matches that are close.
This will then be populated into a listbox that they can choose from.

I would be very elated if I could get this to work. Thank you
Kevin

Kevin:

For the first part, use the macro recorder to record the process of using
Find, with Options set as preferred (do not check "Match entire cell
contents"). By looking at that code, you'll have a good idea of how to
construct your search using the return from your textbox as the text string.

When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches.

That part could be rough! It's going to depend on how you want to define
"closest match". What's close to "poetry"?
Loetry
Poerty
Powtry
Peotry
Potry
Petry

How many permutations are you wanting to go through for a "closest match"??

If you've got a list that must be matched, what about giving the users a
drop-down?
http://www.contextures.com/xlDataVal01.html

Ed

wrote in message
ups.com...
Thanks very much for the help Ed. I think I would like to explore your
advice on using range.find...I'm just not sure how to go about doing
that. Like I said, I am still in the learning process and that is one
area of VBA that I haven't explored. Unfortunately, I don't find the
help files found in excel and vba very helpful.
Could anyone please try to advise me on using range.find to accomplish
what I'm trying to accomplish?
Here is what I would like to do(the simplified version). I have a
textbox and a command button on a worksheet. When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches. So if the user
typed in poetry, and accidently spelled it wrong, when they click the
"search" command button it will check the list where the entry is
supposed to be contained and find one or more matches that are close.
This will then be populated into a listbox that they can choose from.

I would be very elated if I could get this to work. Thank you
Kevin

Maybe the recent thread "wildcard" in this NG will help.

NickHK

"Ed" wrote in message
...
Kevin:

For the first part, use the macro recorder to record the process of using
Find, with Options set as preferred (do not check "Match entire cell
contents"). By looking at that code, you'll have a good idea of how to
construct your search using the return from your textbox as the text
string.

When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches.

That part could be rough! It's going to depend on how you want to define
"closest match". What's close to "poetry"?
Loetry
Poerty
Powtry
Peotry
Potry
Petry

How many permutations are you wanting to go through for a "closest
match"??

If you've got a list that must be matched, what about giving the users a
drop-down?
http://www.contextures.com/xlDataVal01.html

Ed

wrote in message
ups.com...
Thanks very much for the help Ed. I think I would like to explore your
advice on using range.find...I'm just not sure how to go about doing
that. Like I said, I am still in the learning process and that is one
area of VBA that I haven't explored. Unfortunately, I don't find the
help files found in excel and vba very helpful.
Could anyone please try to advise me on using range.find to accomplish
what I'm trying to accomplish?
Here is what I would like to do(the simplified version). I have a
textbox and a command button on a worksheet. When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches. So if the user
typed in poetry, and accidently spelled it wrong, when they click the
"search" command button it will check the list where the entry is
supposed to be contained and find one or more matches that are close.
This will then be populated into a listbox that they can choose from.

I would be very elated if I could get this to work. Thank you
Kevin

Thanks, Nick. I had been looking at that when it first started, but didn't
continue, but there's some good stuff in there.

Ed

"NickHK" wrote in message
...
Maybe the recent thread "wildcard" in this NG will help.

NickHK

"Ed" wrote in message
...
Kevin:

For the first part, use the macro recorder to record the process of using
Find, with Options set as preferred (do not check "Match entire cell
contents"). By looking at that code, you'll have a good idea of how to
construct your search using the return from your textbox as the text
string.

When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches.

That part could be rough! It's going to depend on how you want to define
"closest match". What's close to "poetry"?
Loetry
Poerty
Powtry
Peotry
Potry
Petry

How many permutations are you wanting to go through for a "closest
match"??

If you've got a list that must be matched, what about giving the users a
drop-down?
http://www.contextures.com/xlDataVal01.html

Ed

wrote in message
ups.com...
Thanks very much for the help Ed. I think I would like to explore your
advice on using range.find...I'm just not sure how to go about doing
that. Like I said, I am still in the learning process and that is one
area of VBA that I haven't explored. Unfortunately, I don't find the
help files found in excel and vba very helpful.
Could anyone please try to advise me on using range.find to accomplish
what I'm trying to accomplish?
Here is what I would like to do(the simplified version). I have a
textbox and a command button on a worksheet. When the user types in a
particular word, found in a list on another sheet, I would like the
attached macro to return all of the closest matches. So if the user
typed in poetry, and accidently spelled it wrong, when they click the
"search" command button it will check the list where the entry is
supposed to be contained and find one or more matches that are close.
This will then be populated into a listbox that they can choose from.

I would be very elated if I could get this to work. Thank you
Kevin