Lookup

[Dthmtlgod]

I was given this piece of code by an MVP, and I think I am missing a
reference.

dim res as variant
res =
application.lookup(LEFT(range(D5"),3,range("HTC!A2:A200"),range("HTC!b2:B200
"))

I don't get the lookup option after typing in application.

Please advise.

 

[Vasant Nanavati]

Type it in manually; it should work. Worksheet functions are now accessed
through the WorksheetFunction object rather than the Application object,
although the latter still works for backward compatibility. Many experts
still prefer to use the Application object as it has some esoteric
advantages.

 

[Dthmtlgod]

Thank you Vasant

Type it in manually; it should work. Worksheet functions are now accessed
through the WorksheetFunction object rather than the Application object,
although the latter still works for backward compatibility. Many experts
still prefer to use the Application object as it has some esoteric
advantages.

--

Vasant


application.lookup(LEFT(range(D5"),3,range("HTC!A2:A200"),range("HTC!b2:B200

 

[Doug Glancy]

You are missing a double quote before D5 and a right paren.

I think it should be
(LEFT(range("D5"),3),range("HTC!A2:A200"),range("HTC!b2:B200"))

hth,

Doug

 

[Tom Ogilvy]

Looks like one of use hosed it up - since you are missing a parentheses and
a quote.

It should be

dim res as variant
res =
application.lookup(LEFT(range("D5"),3), _
range("HTC!A2:A200"),range("HTC!b2:B200"))
if not iserror(res) then
msgbox res
else
msgbox "No match"
End if

the reason application is used is because it doesn't raise a vba error when
the match is not made and it works consistently, where at least in xl97,
some of the lookup and match functions would not work when using
Worksheetfunction. This may have been fixed in later verions.

But as Vasant said, if you want intellisense, then you have to use
worksheettfunction.

 

[Dthmtlgod]

It was probably me, it is not performing the LOOKUP. I am not sure why, it
is not erroring out though.

Here is the code. Any ideas? I have Res as Variant

z = 5
Do While Range("DCI!A" & z).Value = Date
If CStr(Range("DCI!J" & z).Value) = "000" Then
Res = Application.Lookup(Left(CStr(Range("DCI!D" & z)), 3),
Range("HTC!$A$2:$A$200"), Range("HTC!$B$2:$B$200"))
If Not IsError(Res) Then
Range("DCI!J" & z).Value = Res
Else
Range("DCI!J" & z).Value = "Not Found-" & Res
End If
End If
z = z + 1
Loop

 

[Tom Ogilvy]

I would try it with the formula in a cell and see if it works

=Lookup(Left(DCI!D5,3),HTC!$A$2:$A$200,HTC!$B$2:$B$200)

If it works there, then it should work in your code.

Also, make sure the Left(DCI!D5,3) is returning what you think it should
(when using code)

Sub Tester9()
z = 5
msgbox Left(CStr(Range("DCI!D" & z)), 3)
End Sub

if you are trying to get the first three letters of a month and the cell
contains a date, you may need to use
Sub Tester9()
z = 5
msgbox Left(Range("DCI!D" & z).Text, 3)
End Sub