Object variable or with block not set

Hi,

im trying to make a small vba application in excell to manage all of my compagny's finances. I just got started, i only got 2 user forms up to now, the first form lists all clients and the second one will show all the informations on the client selected in the first form. When i load the next form, everything is sweet, but when i started putting code in the UserForm_Initialize() method, i started to get runtime error 91 Object variable or with block not set. Ill post the code that i think is relevant and i would really appreciate some of ur help.

in the first form
Private Sub cmdSuivant_Click()
paramClient = Mid(lstClients, 1, InStr(1, lstClients, " ", vbTextCompare))

Unload Me
Load frmClient
frmClient.Show
End Sub

in the second form
Private Sub UserForm_Initialize()
Dim lign As Integer
Dim lRange As Range

Set lRange = Sheet4.Range("A4:A500")

lign = lRange.Find(paramClient).row
msgbox lign
End Sub

in a module
Public paramClient As String

i think the error is related to the lign = lRange.Find(paramClient).row because if i put it in comment, everything is nice.

I would really appreciate ur help
Thanks

That error means you haven't found the paramClient in the range lrange.
Find returns "nothing" when it doesn't find the target. Then when you do

lign = Nothing.row

you get the error you describe.

What you need to do is

set lRange1 = lRange.Find(paramClient)
if lRange1 is nothing then
msgbox "Not Found"
Else
msgbox lRange1.Row
End if

--
Regards,
Tom Ogilvy



"Benny5788" <[email protected]> wrote in
message news:[email protected]...
>
> Hi,
>
> im trying to make a small vba application in excell to manage all of my
> compagny's finances. I just got started, i only got 2 user forms up to
> now, the first form lists all clients and the second one will show all
> the informations on the client selected in the first form. When i load
> the next form, everything is sweet, but when i started putting code in
> the UserForm_Initialize() method, i started to get runtime error 91
> Object variable or with block not set. Ill post the code that i think
> is relevant and i would really appreciate some of ur help.
>
> _in_the_first_form_
> Private Sub cmdSuivant_Click()
> paramClient = Mid(lstClients, 1, InStr(1, lstClients, " ",
> vbTextCompare))
>
> Unload Me
> Load frmClient
> frmClient.Show
> End Sub
>
> _in_the_second_form_
> Private Sub UserForm_Initialize()
> Dim lign As Integer
> Dim lRange As Range
>
> Set lRange = Sheet4.Range("A4:A500")
>
> lign = lRange.Find(paramClient).row
> msgbox lign
> End Sub
>
> _in_a_module_
> Public paramClient As String
>
> i think the error is related to the lign = lRange.Find(paramClient).row
> because if i put it in comment, everything is nice.
>
> I would really appreciate ur help
> Thanks
>
>
> --
> Benny5788
> ------------------------------------------------------------------------
> Benny5788's Profile:
http://www.excelforum.com/member.php...o&userid=24975
> View this thread: http://www.excelforum.com/showthread...hreadid=386132
>