I agree and if I had more control over the data, I'd clean it up but I have
no control over it and the person that controls it doesn't see a need to
clean it up. Kind of a kludge work around I know, but it works for the
people that need to actually print the forms, so I guess I'll just have to
live with it.

Thanks for all your help, Tom. You always make my life so much easier.

Ken Loomis

"Tom Ogilvy" wrote in message
...
Sounds like your data is dirty. Aren't these treatment or condition codes
which should be unique as a whole. Seams like you wouldn't want to search
for just a part of the string.

--
Regards,
Tom Ogilvy

"Ken Loomis" wrote in message
...
I changed that search line to:

Set rng = Worksheets("Classes").Columns("C").Find(EnteredCRN , _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)


and it works now. I had tried removing the "lookat:=xlWhole" think that
the
default was 'xlPart' but I guess not.

Thanks for all the help. I'm sure I'll be needing more real soon.

Ken


"Ken Loomis" wrote in message
...
Thanks, Tom. That did the trick, I think. Well, it at least got me
further
along.

It will find the CRN as long as I paste it the worksheet that contains
the
database into the cell on the search page.

However, if I type that CRN in, which is what we need to do, it does
not
find.

Any ideas what could cause that?

Thanks,
Ken

PS. Although I keep trying this because I really want to learn this VBA
stuff, at some point I wonder if it isn't just easier, though far less
elegant I'm sure, to right a binary search in VBA.


"Tom Ogilvy" wrote in message
...
Dim rng as Range

set rng = Worksheets("Classes").Columns("C").Find(EnteredCRN , _
LookIn:=xlValues, lookat:=xlWhole)
if not rng is nothing then
FoundInRow = rng.row
else
msgbox EnteredCRN & " was not found"
exit sub
End sub

--
regards,
Tom Ogilvy


"Ken Loomis" wrote in message
...
I am getting a "Object Variable or With Block Variable Not Set"
error
in
the following line from the code below:

FoundInRow = Worksheets("Classes").Columns("C").Find(EnteredCRN ,
_
LookIn:=xlValues, lookat:=xlWhole).Row

I just don't do enough of this Excel VBA programming to be able to
figure
that out. Can someone please help?

Thanks,
Ken



Sub FindCRN()
' On Error GoTo errorHandler
Dim EnteredCRN As String
Dim FoundInRow As Integer

EnteredCRN = Worksheets("Enter Data").Range("B4").Value
MsgBox ("EnteredCRN = " & EnteredCRN)
FoundInRow = Worksheets("Classes").Columns("C").Find(EnteredCRN ,
_
LookIn:=xlValues, lookat:=xlWhole).Row
MsgBox ("FoundInRow = " & FoundInRow)
Sheets("Enter Data").Range("B7").Value =
Sheets("Classes").Range("A"
&
FoundInRow)
Sheets("Enter Data").Range("B8").Value =
Sheets("Classes").Range("B"
&
FoundInRow)
Sheets("Enter Data").Range("B9").Value =
Sheets("Classes").Range("F"
&
FoundInRow)
Sheets("Enter Data").Range("B10").Value =
Sheets("Classes").Range("H"
&
FoundInRow)
Sheets("Enter Data").Range("B11").Value =
Sheets("Classes").Range("I"
&
FoundInRow)
Sheets("Enter Data").Range("B5").Value =
Sheets("Classes").Range("D"
&
FoundInRow)

End
errorHandler:
MsgBox "That CRN was not found......Please try again")
End Sub