Instr, in its simplest form, determines if the second string parameter exists
in the first string parameter and it begins searching at 1st character of the
longer (first) string parameter. If the second string is within the first,
then a positive value is returned, if it wasn't found, then a zero is
returned. In VBA we can equate the positive value to True and a zero to
False for testing in code.
Instr("abcdef","cde") would return 3, or be considered True.
Instr("abcdef","efg") would return 0, or be considered False.
In this simple form, the test is case sensitive; that is:
Instr("abcdef","Abc") would return 0, or be considered false: "abc"<>"Abc"
But you can make it case insensitive by using a couple of optional parameters:
You can specify the starting position to start match at ahead of the first
string parameter, and you can add a parameter after the 2nd string to tell it
to use a 'textual' comparison that is case insensitive. If you use the 2nd,
you must use the 1st optional parameter like:
Instr(1,"abcdef","Cde",1) will return 3, or be considered True. In this
form, the case is ignored.
Given all that, here's some code that will start you off to a
case-insensitive search, hopefully you can adapt it to your setup. Change
sheet names and various cell / column identifiers as needed. Doesn't clear
previous search results - you get to deal with that on your own?
Sub TOC_Search_CaseInsensitive()
'sheet name of sheet they select category from
Const srchSheetName = "Sheet1"
'address of cell where selected category is
Const srchCatChosen = "A1"
'address of 1st cell to put search results
Const srchResultStart = "A5"
'sheet name to perform search in
Const tocSheetName = "TOC"
'column ID of 1st column to examine on TOC sheet
'assume columns to search are
'contiguous as A,B and C or F, G and H
Const firstSCol = "A"
Dim srchSheet As Worksheet
Dim tocSheet As Worksheet
Dim showResults As Range
Dim srchRange As Range
Dim anyCell As Range
Dim searchValue As String
Dim lastRow As Long
Dim rOffset As Long
Set srchSheet = Worksheets(srchSheetName)
Set showResults = srchSheet.Range(srchResultStart)
'get category to seek
searchValue = srchSheet.Range(srchCatChosen)
'find last row to search in
Set tocSheet = Worksheets(tocSheetName)
lastRow = _
tocSheet.Range(firstSCol & Rows.Count).End(xlUp).Row
'use "2:" if 1st row is titles/header row.
Set srchRange = tocSheet.Range(firstSCol & "1:" & _
firstSCol & lastRow)
'this search is case insensitive "Hello" = "hello"
For Each anyCell In srchRange
If InStr(1, anyCell, searchValue, 1) Or _
InStr(1, anyCell.Offset(0, 1), searchValue, 1) Or _
InStr(1, anyCell.Offset(0, 2), searchValue, 1) Then
'copy to srchSheet
showResults.Offset(rOffset, 0) = anyCell
showResults.Offset(rOffset, 1) = anyCell.Offset(0, 1)
showResults.Offset(rOffset, 2) = anyCell.Offset(0, 2)
rOffset = rOffset + 1 ' for next match
End If
Next ' end of search
End Sub
