Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
Why does this not work:
Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
DG,
Try it like this: TESTVAR = WorksheetFunction.VLookup("COBRA", Range("A1:D20"), 4) -- Hope that helps. Vergel Adriano "DG" wrote: Why does this not work: Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
Since you're matching on a string, wouldn't you want the match to be exact? I'm
gonna assume that you'd answer yes: Option Explicit Sub Test() dim Res as variant dim VlookupRng as range dim WhatWord as string set vlookuprng = worksheets("dataquery").range("a1:d20") whatword = "Cobra" res = application.vlookup(whatword, vlookuprng, 4, false) if iserror(res) then msgbox "No match!" else msgbox res end if End Sub (Untested, uncompiled) You could have used: res = application.vlookup("cobra", worksheets("dataquery").range("a1:d20"), _ 4, false) DG wrote: Why does this not work: Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
Sub TEST()
Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", Range("A1:D20"), 4) End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DG" wrote in message ... Why does this not work: Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
I tried that and got the same error message.
That's when I put the Sheets("DataQuery").Select statement in thinking that it didn't have the focus on any sheet. But still getting the same error. DG "Vergel Adriano" wrote in message ... DG, Try it like this: TESTVAR = WorksheetFunction.VLookup("COBRA", Range("A1:D20"), 4) -- Hope that helps. Vergel Adriano "DG" wrote: Why does this not work: Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VLookup Function in VB
You are correct I did want it to be exact. I haven't tried the long version
but your second example worked. Thanks. "Dave Peterson" wrote in message ... Since you're matching on a string, wouldn't you want the match to be exact? I'm gonna assume that you'd answer yes: Option Explicit Sub Test() dim Res as variant dim VlookupRng as range dim WhatWord as string set vlookuprng = worksheets("dataquery").range("a1:d20") whatword = "Cobra" res = application.vlookup(whatword, vlookuprng, 4, false) if iserror(res) then msgbox "No match!" else msgbox res end if End Sub (Untested, uncompiled) You could have used: res = application.vlookup("cobra", worksheets("dataquery").range("a1:d20"), _ 4, false) DG wrote: Why does this not work: Sub TEST() Dim TESTVAR As Integer Sheets("DataQuery").Select TESTVAR = Application.WorksheetFunction.VLookup("COBRA", "A1:D20", 4) End Sub I get a Run-time error '1004' Unable to get the VLookup property of the WorksheetFunction class Data looks something like this: Supplier Item Qty Cost AEARO AEA 123 5 2.50 COBRA COB 123 8 4.50 etc... DG -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine Combo Box function with Vlookup function | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions | |||
how do I write a vlookup function within an iserror function so t. | Excel Worksheet Functions | |||
I want to use Vlookup function and AND function in a single formu. | Excel Worksheet Functions |