Hello from Steved

Hello Myrna please excuse me I sometimes do not
explain myself to well, I am typing 100637 in column
D you are correct in the name going in the next column.

Ok what have I got. I have over 1,000 names, each with a
code ie 100637, I allow for 100 rows off inputing of
codes,from D6 to D105.

as an example i cold put 100637 anywhere between D6 and
D105.



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


.