How to use lookup Function in vba

Hi
I have write the following Line in "Worksheet_Change" event

///////////////////////////////////////////////////////////////////////////////////////
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

lookup_value = Sheet1.Application.WorksheetFunction.Lookup(Target.Value, A2:A5, B2:B5)


Application.EnableEvents = True

End Sub
///////////////////////////////////////////////////////////////////////////////////////

but all the time i recieve the following Error from the compiler.
---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Expected: list separator or )
---------------------------
OK Help
---------------------------

Am i doing something wrong? Please help me.

Thanks in advance.
Regards.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

lookup_value = _
Sheet1.Application.WorksheetFunction.Lookup(Target.Value,
Range("A2:A5"),Range("B2:B5"))
Application.EnableEvents = True

End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"ZarrinPour" <[email protected]> wrote
in message news:[email protected]...
>
> Hi
> I have write the following Line in "Worksheet_Change" event
>
>
////////////////////////////////////////////////////////////////////////////
///////////
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Application.EnableEvents = False
>
> lookup_value =
> Sheet1.Application.WorksheetFunction.Lookup(Target.Value, A2:A5,
> B2:B5)
>
>
> Application.EnableEvents = True
>
> End Sub
>
////////////////////////////////////////////////////////////////////////////
///////////
>
> but all the time i recieve the following Error from the compiler.
> ---------------------------
> Microsoft Visual Basic
> ---------------------------
> Compile error:
>
> Expected: list separator or )
> ---------------------------
> OK Help
> ---------------------------
>
> Am i doing something wrong? Please help me.
>
> Thanks in advance.
> Regards.
>
>
> --
> ZarrinPour
> ------------------------------------------------------------------------
> ZarrinPour's Profile:
http://www.excelforum.com/member.php...o&userid=32375
> View this thread: http://www.excelforum.com/showthread...hreadid=521465
>