I was concentrating on figuring out what you are "up to", and I typed the code
directly into the ng message, not into an event macro in a worksheet.

The first line of code should look like this:

Private Sub Worksheet_Change(ByVal Target As Range)

You can just copy that line from this message and paste it over the 1st line
in your worksheet event module.

On Sun, 10 Oct 2004 18:53:43 -0700, "Steved"
wrote:

ps

You have what I want but am getting

Compile error:

Procedure declaration does not match description of event
or procedure having the same name

Can you help me please on this one.

Thankyou very much indeed.




-----Original Message-----
I don't understand what you mean by "D6 is a code I type
in, i.e. 100637".

Are you typing "D6" or "100637"? Or do you mean that you
type the number
100637 in cell D6?

Are you trying to say that you want this routine to fire
whenever Target is a
cell in the range D6 to D105, and in that case you want
it to take the value
you just typed, look it up on the Audit Team sheet, and
put the corresponding
name in some other cell? If so, what cell? The cell that
used to contain the
formula? What cell is that? You haven't said.

Maybe you want something like this:

Sub Worksheet_Change(Target AS Range)
Dim X As Variant

If Target.Column = 4 Then 'D
If Target.Row = 6 and Target.Row <= 105
Then 'between rows 6 and 105

X = Application.VLOOKUP(Target.Value, _
Worksheets("Audit Team").Range("$A$2:$F$2000),
6, 0)

'what are we supposed to do with the name we just
fetched???
'I'm going to take a GUESS that we are supposed to
put it in
'the cell to the right of Target

Application.EnableEvents = False

With Target.Offset(0, 1)
If IsError(X) Then
.ClearContents
Else
.Value = X
End If
End With

Application.EnableEvents = True

End If 'row is 6 through 105
End If 'column is D
End Sub

What the above will do is, if you type a valid code in,
say, D10, it will put
the corresponding name in E10. If it's not valid, it
clears E10.


On Sun, 10 Oct 2004 16:59:48 -0700, "Steved"
wrote:

Hello from Steved

First of please forget my attempt of trying write the
code
myself.


Ok D6 is a code I type in ie "100637", it looks in sheet
named Audit Team and finds "100637", goes across 6
columns
and finds the association in this case a persons surname
ie "Jones".
I need the below to look in D6:D105, (99 rows)

ps a least I made a go off it.
Evaluate("IF(ISNA(VLOOKUP(D6,'Audit Team'!" & _
"$A$2:$F$2000,6,0)),"""",VLOOKUP(D6,'Audit Team'!" & _
"$A$2:$F$2000,6,0))")

Thankyou





-----Original 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.
.


.