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

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 May wrote:

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

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



"Jim May"
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

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

"Jim Cone" wrote:


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



"Jim May"
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

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...



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

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" wrote:


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...



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

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?..

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?

Jim May wrote:

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" wrote:


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...



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



--

Dave Peterson