Why R/T 91?

[Guest]

My cell C6 contains Vaughn

Sub FindCell()
With ActiveSheet.Range("B4:G9")
Set c = .Find(What:="Vaughn", LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns)
End With
MsgBox "Subject value is in cell " & c.Address '<< error here !!
End Sub

 

[Dave Peterson]

Option Explicit 'declare those variables!
Sub FindCell()
dim c as range '<--added
With ActiveSheet.Range("B4:G9")
Set c = .Find(What:="Vaughn", LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns)
End With
if c is nothing then
msgbox "Not found"
else
MsgBox "Subject value is in cell " & c.Address '<< error here !!
end if
End Sub

If "Vaughn" wasn't found, then c is nothing--not a range with an address.

 

[Jim Cone]

Try changing xlWhole to xlPart.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jim May" <[email protected]>
wrote in message
My cell C6 contains Vaughn

Sub FindCell()
With ActiveSheet.Range("B4:G9")
Set c = .Find(What:="Vaughn", LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns)
End With
MsgBox "Subject value is in cell " & c.Address '<< error here !!
End Sub

 

[Guest]

Why? from xlWhole to xlPart - It's not obviousl to me...

 

[Jim Cone]

It will find your specific text even if the cell contains other characters or spaces.
Jim Cone



"Jim May"
wrote in message
Why? from xlWhole to xlPart - It's not obviousl to me...

 

[Guest]

Thanks Jim, but my cell C6 ONLY contains Vaughn - I did an F2 on it
and it has no extra anything.. I even did a = Len(C6) and got 6
any added thoughts?..

 

[Jim Cone]

No other ideas other then try reentering the text in the cell.
Your code does work for me.
However, Dave Peterson's post shows the proper approach to use.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jim May"
wrote in message
Thanks Jim, but my cell C6 ONLY contains Vaughn - I did an F2 on it
and it has no extra anything.. I even did a = Len(C6) and got 6
any added thoughts?..

 

[Dave Peterson]

One of the quirks of .find is that it shares the parms with the user (through
the user interface).

Since you only specified:

Set c = .Find(What:="Vaughn", LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns)

maybe the reason your code didn't find it is because one of the other parms
wasn't what you expected. If your cell contains VAUGhn and matchcase:=true (set
by a previous .find statement or by the user), then this .find would fail.

I'd specify all those parms that you see in VBA's help.

=====
If that's not it (and it isn't a hard to see typo <bg>), then the only other
time I've seen .find fail is when I'm using merged cells.

You're not using those evil merged cells are you?

 

[Guest]

Dave.. very thorough of you...
Thanks,
Jim

One of the quirks of .find is that it shares the parms with the user (through
the user interface).

Since you only specified:

Set c = .Find(What:="Vaughn", LookAt:=xlWhole, _
LookIn:=xlValues, SearchOrder:=xlByColumns)

maybe the reason your code didn't find it is because one of the other parms
wasn't what you expected. If your cell contains VAUGhn and matchcase:=true (set
by a previous .find statement or by the user), then this .find would fail.

I'd specify all those parms that you see in VBA's help.

=====
If that's not it (and it isn't a hard to see typo <bg>), then the only other
time I've seen .find fail is when I'm using merged cells.

You're not using those evil merged cells are you?