I have to produce a table of contents that is driven by a keyword from a list
of 2500 categories, when the user selects the category the macro go to
another sheet and then get the table contents and paste them back on the
search sheet, the table contains 3 columns of data, the category, new
category and then a code can someone help as I have never used Instr before.

Thanks

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



"GBH99" wrote:

I have to produce a table of contents that is driven by a keyword from a list
of 2500 categories, when the user selects the category the macro go to
another sheet and then get the table contents and paste them back on the
search sheet, the table contains 3 columns of data, the category, new
category and then a code can someone help as I have never used Instr before.

Thanks

You may?? want to take a look at FIND and FINDNEXT.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"GBH99" wrote in message
...
I have to produce a table of contents that is driven by a keyword from a
list
of 2500 categories, when the user selects the category the macro go to
another sheet and then get the table contents and paste them back on the
search sheet, the table contains 3 columns of data, the category, new
category and then a code can someone help as I have never used Instr
before.

Thanks