"Object Variable or With Block Variable Not Set" error help request

[Ken Loomis]

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

 

[Tom Ogilvy]

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

 

[jjk]

..Row returns a Range object.
Try using set:
Use the .Row property of the range that is returned.
If you declare declare the variables then the VBE will give you
autocomplete options. It really helps in exploring the VBA.

Dim FoundInRow as Range
Set FoundInRow = Worksheets("Classes").Columns(­"C").Find(EnteredCRN,
_
LookIn:=xlValues, lookat:=xlWhole).Row
MsgBox ("FoundInRow = " & FoundInRow.Row)

Regards,
Jayant

 

[Tom Ogilvy]

You can't set a range variable to take on a Long or Integer value. Set
requires an object. Also Find isn't always successful (the case here) so
you have to check if it was successful.

Dim FoundInRow as Long
Dim rng as Range
Set rng = Worksheets("Classes").Columns(­"C").Find(EnteredCRN, _
LookIn:=xlValues, lookat:=xlWhole)
' now check if the CRN was found
if not rng is nothing then
Foundrow = rng.row
MsgBox ("FoundInRow = " & FoundRow)
Else
msgbox EnteredCRN & " not found"
End If

--
Regards,
Tom Ogilvy


..Row returns a Range object.
Try using set:
Use the .Row property of the range that is returned.
If you declare declare the variables then the VBE will give you
autocomplete options. It really helps in exploring the VBA.

Dim FoundInRow as Range
Set FoundInRow = Worksheets("Classes").Columns(­"C").Find(EnteredCRN,
_
LookIn:=xlValues, lookat:=xlWhole).Row
MsgBox ("FoundInRow = " & FoundInRow.Row)

Regards,
Jayant

 

[Bob Phillips]

Ken,

It isn't finding it. Try this

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

EnteredCRN = Worksheets("Enter Data").Range("B4").Value
MsgBox ("EnteredCRN = " & EnteredCRN)
On Error Resume Next
FoundInRow = Worksheets("Classes").Columns("C").Find(EnteredCRN, _
LookIn:=xlValues, lookat:=xlWhole).Row
On Error GoTo 0
If FoundInRow = 0 Then
GoTo errorHandler
Else
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
End If

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



--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Loomis]

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.

 

[Ken Loomis]

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

 

[Tom Ogilvy]

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.

 

[Ken Loomis]

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