Lookup
 

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.

Lookup
 

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

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

Lookup
 

Thank you Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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

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

Lookup
 

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("H TC!b2:B200"))

hth,

Doug

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

Lookup
 

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.

--
Regards,
Tom Ogilvy




"Dthmtlgod" wrote in message
...
Thank you Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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

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

Lookup
 

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

--
Regards,
Tom Ogilvy




"Dthmtlgod" wrote in message
...
Thank you Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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

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

Lookup
 

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
--
Regards,
Tom Ogilvy



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

--
Regards,
Tom Ogilvy




"Dthmtlgod" wrote in message
...
Thank you Vasant


"Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message
...
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

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