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


--
Benny5788
------------------------------------------------------------------------
Benny5788's Profile: http://www.excelforum.com/member.php...o&userid=24975
View this thread: http://www.excelforum.com/showthread...hreadid=386132

Object variable or with block not set
 

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