.FIND failing - can you see the error?

[aMember]

BookTitle is a variant input parameter to the routine that contains this line. This always fails on .FIND. What am I doing wrong? Using Excel '97

Dim Titles As Range
with Sheets("List&quot
Set Titles = .Range(.Range("Titles&quot.Address, .Range("B65535&quot.End(xlUp).Address)

Set theBook = Titles.Find(What:=BookTitle,
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

end with

 

[LoNeRaVeR]

aMember:

The code works provided that you have a Sheet named Lists, a Range defined as Titles, and have a value assigned to the variable BookTitle.

Dim Titles As Range
 With Sheets("List")
  Set Titles = .Range(.Range("Titles").Address, _
    .Range("B65535").End(xlUp).Address)
  Set theBook = Titles.Find _
    (What:=BookTitle, _
    LookIn:=xlValues, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
 End With
End Sub

Regards, LoNeRaVeR

 

[RobertT687]

Just a guess. I think you are missing a continuation character following What:=BookTitle,. Try:

Set theBook = Titles.Find(What:=BookTitle, _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

 

[aMember]

LoNeRaVeR,
Did you try this?

>The code works provided that you have a Sheet named Lists,
> a Range defined as Titles, and have a value assigned to
>the variable BookTitle.

Titles is cell B1
Sheets(1) = Lists
BookTitle is an input paramter of type Variant

It doesn't work for me. Any other suggestions?

 

[xlbo]

what does:
Range("Titles&quot.Address

evaluate to - ie what is that address ??
The only thing I could see that wopuld cause the code to fail is that the range isn't set up properly Rgds
~Geoff~

 

[aMember]

Thanks, Geoff,

Range("Titles&quot.address = Sheets("Lists&quot.Range("B1&quot

Andrea

 

[LoNeRaVeR]

amember

Your original code was:

  With Sheets("List")

This may not be the answer, but is the code exactly as shown above? Maybe the answer should be

  With Sheets("

Lists

")

Is the problem because the sheet is not named List, but is actually Lists? LoNeRaVeR

 

[aMember]

Sorry...typo...it is "Lists".

Another interesting tid-bit.

The original code (public sub CheckEnteredTitle ) is in Module1.
It is called from a command button on sheet Lists.
In Module1 I created:

Sub test()
Dim theBook
theBook = "b"
Dim BookRead As Boolean
Dim ReadWhen
CheckEnteredTitle theBook, BookRead, ReadWhen
End Sub

I stepped through the code and this worked fine.
I then went back to the command button on the sheet and clicked on it and the code worked. Great...now what did I do to get it to work. Tried it again and got "Unable to get the FIND property of the Range class", which is what I had been getting.

Went back and tried the TEST routine again, it also failed with the same error message.

OK, well made a copy of Test called Test2.
Ran Test2, worked fine. Tried Test again it it works.
Now I can run Test and Test2 without it failing and repeatedly.

I have heard about FIND needing to be in a module. Has anyone ever heard this or if it needs to be called from a module?

Thanks for all the help,
Andrea

 

[rmikesmith]

Andrea,

I am running Excel 97 also. I mocked this up using the posted code and setting sheet and range names as indicated. It works just as it should; so I don't think there is a problem with the code, per se. I assume this is wrapped in a function. Could you post the entire function as well as the snippet of code that calls this function?


Regards,
Mike

 

[aMember]

There isn't much to the function right now as I am just starting with this task. Imagine my frustration when I can't get past this darn problem.

But, I think I got it. I've had problems before with "Take Focus On Click" property. Well, it just bit me again. I set it to false and lo-and-behold --- it worked!

Thanks to all who tried to help!
Andrea