PDA

View Full Version : Find function



hearts006
03-09-2009, 02:22 AM
Hi all,

I am writing a function to search for "monday", if it is not present, then it will search for "- monday". if both are not present, it will search for "-monday".

But the below code snippet, fails at second find, ie, tryin to find for "- monday"

--------------------------
Assume strWord = Monday
--------------------------

public function findWord()

strWord1 = strWord

find:

Range("A1").Select
Columns("A:A").Select

On Error GoTo ErrHandler1:
Cells.find(What:=strWord1, After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate

strTransWord = Range("B" & ActiveCell.Row).Value

ErrHandler1:
If Err.Number = 91 Then
strTransWord = ""
End If

If strTransWord = "" Then
If strWord1 = strWord Then
strWord1 = "- " & strWord
Err.Clear
GoTo find:
ElseIf strWord1 = "- " & strWord Then
strWord1 = "-" & strWord
Err.Clear
GoTo find:
End If
End If

End Function

Bob Phillips
03-09-2009, 02:53 AM
Set cell = Columns("A:A").find( _
What:=strWord, _
After:=Range("A1"), _
LookIn:=xlValues, _
LookAt:=xlPart)

If Not cell Is Nothing Then strTransWord = cell.Value

hearts006
03-09-2009, 04:37 AM
The above code will search for only one word right? What about the other possibilities of the same word?i mean - Monday and -Monday

Bob Phillips
03-09-2009, 05:14 AM
Try it and see.

hearts006
03-12-2009, 01:52 AM
Hi,

I want to search for the value in column A and then take the value from column B. Your code gets the value of col A only...

Bob Phillips
03-12-2009, 01:53 AM
column B is just



cell.Offset(0,1).Value

hearts006
03-12-2009, 03:49 AM
The find function returns the value of the required cell only when .Activate is used. But when i use that, i am gettin error. the above code always gives me row 1 only

Bob Phillips
03-12-2009, 04:07 AM
There is no .Activate in the code or any needed.