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

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" wrote in
message ...

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