How to search for perticular word in excel spreadsheet column 1

[ndp]

Hi,
I am customizing a macro in Excel spreadsheet. What I want to do is go through each row in a column, and find matching value from another spreadsheet. It's kind of look up table. I want to know if there is function for searching a perticular word in a column and can get the row number.
I would appreciate any suggestion.

Thanks in advance,
ndp

 

[PHV]

Perhaps VLookUp ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Molby]

If the word can appear in the column more than once, why not use an autofilter. Then you can use activecell.row to get the value of the row.

 

[Nikita6003]

If you're using VBA, try

Dim l_rngRange As Range

Set l_rngRange = ThisWorkbook.Worksheets("Sheet1").Columns("C:C").Find("case")
If l_rngRange Is Nothing Then MsgBox "Word not found"

This code tries to find the word case in column C of Sheet1
If the word is found, l_rngRange is set to the cell containing the word [tt]case[/tt]. If the word is NOT found, the messagebox pops up.

NB: this Find command will look for the word [tt]case[/tt] anywhere in a cell. If you want to force Excel to just look for cells containing "[tt]ase[/tt] then use the following:

Set l_rngRange = ThisWorkbook.Worksheets("Sheet1").Columns("C:C").Find("case", , , xlWhole)

NB2: use [tt]FindNext[/tt] to repeat the search downwards in the column; use [tt]FindPrevious[/tt] to repeat the search upwards in the column

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[ndp]

Thanks so much for these suggestions. How do I get the row number?

 

[Molby]

As I said, bob$ = activecell.row will set bob$ as the row number.

 

[Chance1234]

have a look at the INDEX and Match Functions, vlookup is fodder left over from lotus 123. they should give you want you want but you might be worth rewording your question.

as its open to interpretation,



Filmmaker, gentleman and i have a new site 3/4 working

http://WWW.5YLAC.S5.COM

http://WWW.HOWTOBESPOOKY.CO.UK

 

[Nikita6003]

Using the VBA example all you'd neet to do is to use the Row property of the range:

l_lRowFound = l_rngRange.Row

HTH

Cheers
Nikki
Look, mommy, I'm flying!

 

[ndp]

Thanks everybody,
Finally I used .Find function which Nikki suggested. It seems to be working. Thanks Nikita for giving me code example too which saved lot of my time searching syntex.

Thanks again,
ndp