Search Array of strings for an exact match of string containing more than one word (case insensitive)

[sctlippert2]

This code works, but only if it is one word... how do I change it to look for an exact phrase?

InStr(1, ArrayContainingStrings(i, 1), WORD, vbTextCompare) <> 0

 

[Micron]

Pass the entire string being sought to the Instr "string to find" parameter. Cannot be specific without examples of what is being looked for and what is being searched.
EDIT - what you posted doesn't make sense when taken verbatim. There would have to be a logical comparison of some sort, such as IF.

 

[sctlippert2]

Here is the full code:

Sub InputTopic()
Dim LastRow As Long, LastCol As Long, LastColLetter As String
Dim VerseArray() As Variant, i As Long
Dim SourceWS As Worksheet, Topic As String, ws As Worksheet

Topic = InputBox("Enter Topic or Word to search for...")
 
        'Check If Sheet Already Exists
        For Each ws In ThisWorkbook.Worksheets
            If InStr(1, ws.Name, Topic, vbTextCompare) <> 0 Then GoTo errorhandler:
        Next ws

Sheets.Add(After:=Sheets(Sheets.count)).Name = Topic
    Columns("A:A").ColumnWidth = 100                    'Columns.AutoFit
    Columns("A:A").WrapText = True
    Columns("B:B").ColumnWidth = 10

Set SourceWS = Sheets("PROVERBS")
SourceWS.Activate
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.count     'FIND # of LAST Row w/ DATA
LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Columns.count  'FIND # of LAST Column w/ DATA
LastColLetter = Split(Cells(1, LastCol).Address, "$")(1)         'CHANGE # to Actual LETTER of COLUMN

VerseArray = Range("A1:" & LastColLetter & LastRow)   'inputs data automatically into array
x = 0
For i = LBound(VerseArray, 1) To UBound(VerseArray, 1)
    If InStr(1, VerseArray(i, 1), Topic, vbTextCompare) <> 0 Then
            x = x + 1: Sheets(Topic).Select
        Sheets(Topic).Range("A" & x).Value = VerseArray(i, 1)
        Sheets(Topic).Range("B" & x).Value = VerseArray(i, 2)
    End If

Next i

For k = Sheets.count To 5 Step -1
    Application.DisplayAlerts = False
    If Sheets(k).Range("A1").Value = 0 Then Sheets(k).Delete
    Application.DisplayAlerts = True
Next k
Sheets("Input Topic").Activate
Exit Sub

errorhandler: Sheets("Input Topic").Select: _
    MsgBox ("That TOPIC or Worksheet already exists... click <OK> and choose another.")

End Sub

 

[sctlippert2]

Here is the full code:

Sub InputTopic()
Dim LastRow As Long, LastCol As Long, LastColLetter As String
Dim VerseArray() As Variant, i As Long
Dim SourceWS As Worksheet, Topic As String, ws As Worksheet

Topic = InputBox("Enter Topic or Word to search for...")
 
        'Check If Sheet Already Exists
        For Each ws In ThisWorkbook.Worksheets
            If InStr(1, ws.Name, Topic, vbTextCompare) <> 0 Then GoTo errorhandler:
        Next ws

Sheets.Add(After:=Sheets(Sheets.count)).Name = Topic
    Columns("A:A").ColumnWidth = 100                    'Columns.AutoFit
    Columns("A:A").WrapText = True
    Columns("B:B").ColumnWidth = 10

Set SourceWS = Sheets("PROVERBS")
SourceWS.Activate
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Rows.count     'FIND # of LAST Row w/ DATA
LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Columns.count  'FIND # of LAST Column w/ DATA
LastColLetter = Split(Cells(1, LastCol).Address, "$")(1)         'CHANGE # to Actual LETTER of COLUMN

VerseArray = Range("A1:" & LastColLetter & LastRow)   'inputs data automatically into array
x = 0
For i = LBound(VerseArray, 1) To UBound(VerseArray, 1)
    If InStr(1, VerseArray(i, 1), Topic, vbTextCompare) <> 0 Then
            x = x + 1: Sheets(Topic).Select
        Sheets(Topic).Range("A" & x).Value = VerseArray(i, 1)
        Sheets(Topic).Range("B" & x).Value = VerseArray(i, 2)
    End If

Next i

For k = Sheets.count To 5 Step -1
    Application.DisplayAlerts = False
    If Sheets(k).Range("A1").Value = 0 Then Sheets(k).Delete
    Application.DisplayAlerts = True
Next k
Sheets("Input Topic").Activate
Exit Sub

errorhandler: Sheets("Input Topic").Select: _
    MsgBox ("That TOPIC or Worksheet already exists... click <OK> and choose another.")

End Sub

each array element contains a whole sentence... I want to be able to search for a phrase as in "fear of the lord", but it only works with ONE word only

 

[sctlippert2]

each array element contains a whole sentence... I want to be able to search for a phrase as in "fear of the lord", but it only works with ONE word only

You will see two different loops, the top one I was testing to see if it would work... forgot to take it out

 

[igold]

You may want to try using Application.InputBox instead... Where the 2 is looking for a string...

Topic = Application.InputBox("Enter Topic or Word to search for...", , , , , , , 2)

 

[sctlippert2]

You will see two different loops, the top one I was testing to see if it would work... forgot to take it out

Scratch that last comment. The first loop checks if the sheet already exists

 

[sctlippert2]

You may want to try using Application.InputBox instead... Where the 2 is looking for a string...

Topic =
[QUOTE="igold, post: 6083894, member: 292562"]
You may want to try using Application.InputBox instead... Where the 2 is looking for a string...

[CODE=vba]
Topic = Application.InputBox("Enter Topic or Word to search for...", , , , , , , 2)

I actually tried that one, it spits out an error message on the second loop
("Enter Topic or Word to search for...", , , , , , , 2)
[/CODE]
[/QUOTE]

 

[sctlippert2]

I actually tried that one, it spits out an error message on the second loop
("Enter Topic or Word to search for...", , , , , , , 2)
[/CODE]

[/QUOTE]
Sorry, not an error message, simply deletes the file because it can't match data

 

[Micron]

If you post data that can be used (with instructions if it's not obvious to anyone else) it would make it easier to help with your code as we cannot see what's going into the array. But not pictures of data.

This
Instr(1,"mary had a little lamb","MARY HAD A LITTLE LAMB",vbTextCompare)
returns 1. This
Instr(1,"I heard that mary had a little lamb","MARY HAD A LITTLE LAMB",vbTextCompare)
returns 14 so it should prove that multiple words that comprise a string can be found within a string. That seems to deal with your initial post. The problem is likely within your array. Note that vbTextCompare is equivalent to 1, which you are using.

Some observations on the code:
Comments within code could help others to better understan what is happening.

I personally would not use an error handler to deal with a sheet that already exists as opposed to just dealing with it in the IF block. That's because it's not an error, but that's me. The exception would be if I was making that scenario into a custom error number that gets ADDED to the error evaluations. However, you're not doing multiple evaluations, or even providing for any other error that may arise (1004 is the one that seems most prevelant in Excel vba). Instead perhaps the msgbox should provide OK/Cancel options. OK reopens the input box, cancel exits sub. That way you don't reinitialize your variables. That's more important when they are objects and have been Set, which you're not doing. Point is, it's not the best habit to get into.

You don't need to activate or select sheets, ranges and such to get at their properties - it slows down code.

Using used range to get last row will include formatted but empty cells. Using Find is considered better.