Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup looks to the prior column if zero appears in the lookup col | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) |