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.

Hi Steved
and how do you want this formula in VBA?. Just as a result in one
cell??

--
Regards
Frank Kabel
Frankfurt, Germany


Steved wrote:
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.

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.

isna is application.worksheetfunction.isna(expression)
vlookup is application.worksheetfunction.vlookup
audit team is sheets("audit team"
a2:f2000 is range("$a$2:$f42000")

if whatever then something else something differen

--
duan
-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=26802

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.


.

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.


.

It isn't clear what your intent is from the code you posted. You have
changed D6 to D6:D105 - not sure what you want to do. Then you are looping
through all the cells in Target - again, unclear what you want to do. You
make no assignment with the results of the formula - so again, unclear what
you want to do.

--
Regards,
Tom Ogilvy

"Steved" wrote in message
...
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.


.

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

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

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


.

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


.

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


.

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


.


.

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


.


.

Thankyou Tom.

"Tom Ogilvy" wrote:

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


.


.