Search Engine [String with Two Values]

[maggie]

I have created the following 'search engine' for an Excel file. This search tool looks for keywords then spits the results out on a "Home" sheet. This works well.

My Question is:
Say I want to search the keyword "Cat". However as the keyword "Cat" also means "Kitten", I would like the engine to return all values that say Cat and Kitten. As well as when "Kitten" is searched, I would like all Kitten and Cat values to be returned. Any Ideas on how to write this into my program. There are only two instances where this occurs so I could write it in per instance.

Ex. Cat also could be searched as Kitten
Dog could also be searched as Puppy. (Only two instances)

Thank you in advance ~ Maggie

Program Below

Private Sub cmdSearch_Click()

Worksheets("Home").Select
Range("A3:G65536").Select
Selection.Clear
Selection.RowHeight = StandardHeight
Dim ws As Worksheet, myvar As String, val1 As Range
Dim val2 As Range, tmp As Range, cnt As Integer
cnt = 0
myvar = InputBox("Please Enter a Keyword:")
If myvar = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
Set val1 = ws.Cells.Find(What:=myvar, LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)
If Not val1 Is Nothing Then
cnt = cnt + 1
Application.Goto val1
ActiveCell.EntireRow.Select
Selection.Copy
Worksheets("Home").Select
Range("A1").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Set tmp = val1
again:


Set val2 = ws.Cells.FindNext(After:=val1)
If val1.Address <> val2.Address And _
tmp.Address <> val2.Address Then
Application.Goto val2
ActiveCell.EntireRow.Select
Selection.Copy
Worksheets("Home").Select
Range("A2").Select
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Set val1 = val2
GoTo again
End If
End If
Next ws
If cnt = 0 Then MsgBox "No Matches Found"
End Sub

[Frosty]

As a generic concept, you're just talking about creating your own array of values where inputting any of those values means you want to search for everything in that array.

So, you can use a multi-dimensional array to define all of your various values, something akin to:

Dim arySearch(1, 5) As String

arySearch(0, 0) = "Dog"
arySearch(0, 1) = "Puppy"
arySearch(0, 2) = "doggy"
arySearch(0, 3) = "barker"
arySearch(0, 4) = "ruffruff"
arySearch(0, 5) = "Licker"
arySearch(1, 0) = "Cat"
arySearch(1, 1) = "Kitten"
arySearch(1, 2) = "Kitty"
arySearch(1, 3) = "Meowmeow"
arySearch(1, 4) = "Purrer"
arySearch(1, 5) = "Aloof"

And then set up your routine to capture the user input, and then you can figure out which area of the array you want to do all your searches for.

However, since it seems you want to do this in Excel, the best place would be the Excel forum, since the specific code you'd get in here would be Word-based.

[maggie]

Apologies that this is in Word Forum not Excel. I appreciate your help though, this will help me on the path to VBA success!

[Frosty]

This is a little more concrete of an example of what I meant:

Sub SearchDemo()
Dim sSearchTerm As String
Dim i As Integer
Dim x As Integer
Dim arySearch(1, 5) As String
Dim bFoundIt As Boolean
Dim xRet As Integer

arySearch(0, 0) = "Dog"
arySearch(0, 1) = "Puppy"
arySearch(0, 2) = "doggy"
arySearch(0, 3) = "barker"
arySearch(0, 4) = "ruffruff"
arySearch(0, 5) = "Licker"
arySearch(1, 0) = "Cat"
arySearch(1, 1) = "Kitten"
arySearch(1, 2) = "Kitty"
arySearch(1, 3) = "Meowmeow"
arySearch(1, 4) = "Purrer"
arySearch(1, 5) = "Aloof"

sSearchTerm = InputBox("What do you want to search for", "Dogs and Cats living together!", "Dog or Cat")

'loop through the first dimensions
For x = 0 To UBound(arySearch)
'and the second dimensions
For i = 0 To UBound(arySearch, 2)
If UCase(sSearchTerm) = UCase(arySearch(x, i)) Then
bFoundIt = True
xRet = x
End If
Next
Next
If bFoundIt Then
'found it, indicate success by using the first term
MsgBox "You want to search for: " & arySearch(xRet, 0)
Else
MsgBox "Didn't find a category for: " & sSearchTerm
End If
End Sub

[maggie]

Thank you!