Re: VLookup Function in VB

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <petersod@xxxxxxxxxxxxxxxx> wrote in message
news:46F03620.CF6447E5@xxxxxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: search 2 Columns Please
    ... Sub FindPart() ... Dim res, saddr As String ... Dim RgToSearch As Range, RgFound As Range ...
    (microsoft.public.excel.programming)
  • Re: How to return an error code from a function
    ... Dim myVal As Variant ... Sub testme() ... Dim res As Double ... Set myError = Err ...
    (microsoft.public.excel.programming)
  • RE: Cancel if the first one is found
    ... End Sub ... Dim res As String, saddr As String ... Dim RgToSearch As Range, RgFound As Range ... Dim secondValue As String ...
    (microsoft.public.excel.programming)
  • Re: Creating a tick by clicking a cell
    ... Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) ... Dim myValues As Variant ... Dim res As Variant ...
    (microsoft.public.excel.misc)
  • Re: Single vs Double
    ... Sub Test() ... Dim X as Single ... up with the exact value 162 fortuitously. ... When using floating point values, you need to check for APPROXIMATE ...
    (microsoft.public.excel.misc)