What you have in the EVALUATE statement is an array formula, looking up each
of the values in the range D6:D105. That statement doesn't return 1 value, it
returns an array of 100 values, one for each cell in D6:D105.

And you execute that same statement once for each cell in Target. What is the
address of Target? What relationship does it have, if any, to the range
D6:D105?

So, for every cell in Target, you have 100 results. What do you want to do
with them?

On Sun, 10 Oct 2004 15:38:29 -0700, "Steved"
wrote:

Thankyou Tom.

I would like to ask for your assistance to have the below
work please

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Application.EnableEvents = FalsFor Each myCell In
Target.Cells
Evaluate ("IF(ISNA(VLOOKUP(D6:D105,'Audit Team'!"
& _
"$A$2:$F$2000,6,0)),"""",VLOOKUP(D6:D105,'Audi t Team'!" & _
"$A$2:$F$2000,6,0))")
End Sub





-----Original Message-----
res = Evaluate("IF(ISNA(VLOOKUP(D6,'Audit Team'!" & _
"$A$2:$F$2000,6,0)),"""",VLOOKUP(D6,'Audit Team'!" & _
"$A$2:$F$2000,6,0))")

--
Regards,
Tom Ogilvy


"Steved" wrote in
message
...
Hello from Steved

Can the below formula be adapted to be put in VBA

=IF(ISNA(VLOOKUP(D6,'Audit Team'!
$A$2:$F$2000,6,0)),"",VLOOKUP(D6,'Audit Team'!
$A$2:$F$2000,6,0))

Thanks for your response.


.