Can this formula be used in VBA

[Steved]

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.

 

[Frank Kabel]

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

 

[Tom Ogilvy]

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

 

[duane]

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

 

[Steved]

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(D6105,'Audit Team'!"
& _
"$A$2:$F$2000,6,0)),"""",VLOOKUP(D6105,'Audit Team'!" & _
"$A$2:$F$2000,6,0))")
End Sub

 

[Myrna Larson]

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

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
D6105?

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

 

[Tom Ogilvy]

It isn't clear what your intent is from the code you posted. You have
changed D6 to D6105 - 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.

 

[Steved]

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 D6105, (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

 

[Myrna Larson]

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.

 

[Steved]

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.

 

[Steved]

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.

 

[Myrna Larson]

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.

 

[Steved]

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.

 

[Tom Ogilvy]

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

 

[Guest]

Thankyou Tom.

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