Why R/T 91?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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...
 
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?..
 
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?
 
Dave.. very thorough of you...
Thanks,
Jim


Dave Peterson said:
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?
Click to expand...
 
Back
Top