You put it in the selectionChange event rather than the Change event.

--
Regards,
Tom Ogilvy

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

I had already fiquered out what the issue was
and had put the same line in as you have below.
But I am having trouble and that is I type in 10037
but get nothing I move the cursor down a cell still
nothing but the moment I go back to the cell I have just
typed in 10037 up pops the name in the cell. I do not know
why this is. Any Ideas on this one

Cheers

And thankyou.



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


.


.