CODEWITHSUNDEEP
excelvba
evoandy
evoandy

Reputation: 660

Excel vba unable to get the Vlookup property

I know there are a lot of these questions on here but I can't see what is going wrong.

I have the following code which checks whether a value is in a column in another workbook.

Dim masterWbk As Workbook
Dim oWbk As Workbook
Dim RowCount As Integer
Dim LookupRange As Range
Dim Exists As Variant
Dim a As Integer
Dim i As Integer
Dim jobnumber As String

    RowCount = WorksheetFunction.CountA(Sheets("Sheet1").Range("A1").EntireColumn)

    masterWbk.Activate

Set LookupRange = masterWbk.Sheets("Sheet1").Range("C1:C100")

    a = 0

    For i = 0 To RowCount - 1

        jobnumber = oWbk.Sheets("Sheet1").Range("A2").Offset(i, 0).Value

      '  On Error GoTo ExistsError:

        Exists = Application.WorksheetFunction.VLookup(jobnumber, LookupRange, 1, False)

Now the value is definitely in the the lookup range and is formatted the same but the Vlookup just won't work. It works fine as an excel formula.

What have I missed?

Upvotes: 1

Views: 13187

Answers (3)

David Zemens
David Zemens

Reputation: 53623

This seems to work fine in Excel 2010...

I would suggest you're likely getting an error with VLOOKUP because you've dimensioned jobNumber as String and despite the worksheet/cell "formatting", the job numbers in your worksheets are likely stored as numeric values.

So, the problem is that "123" <> 123. Test whether jobNumber is numeric, and then make sure you're passing the right sort of data to the function.

If IsNumeric(jobNumber) Then
    Exists = Application.WorksheetFunction.VLookup(CLng(jobNumber), lookupRange, 1, False)
Else:
    Exists = Application.WorksheetFunction.VLookup(jobNumber, lookupRange, 1, False)
End If

Upvotes: 0

grahamj42
grahamj42

Reputation: 2762

I think there is a problem with the documentation of WorksheetFunction.Vlookup. In my tests on Excel 2007, if the search string is found, the value of the corresponding cell is returned. If you search for an exact match, you get the dreaded error 1004.

If you want to use Vlookup,

...

Dim FoundText as String

...

FoundText=""

On Error Resume Next

FoundText=Application.WorksheetFunction.VLookup(jobnumber, LookupRange, 1, False)

On Error Goto 0

Exists= (FoundText <> "")

...

Upvotes: 0

scott
scott

Reputation: 2265

Using find it'd look like this- 

Dim masterWbk As Workbook
Dim oWbk As Workbook
Dim RowCount As Integer
Dim LookupRange As Range
Dim Exists As Variant
Dim a As Integer
Dim i As Integer
Dim jobnumber As String

    RowCount = WorksheetFunction.CountA(Sheets("Sheet1").Range("A1").EntireColumn)

    masterWbk.Activate

Set LookupRange = masterWbk.Sheets("Sheet1").Range("C1:C100")

  a = 0

  For i = 0 To RowCount - 1

  jobnumber = oWbk.Sheets("Sheet1").Range("A2").Offset(i, 0).Value
  If Trim(jobnumer) <> "" Then
    With lookuprange
        Set Rng = .Find(What:=jobnumber, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Exists = 1
        Else
            Exist = 0
        End If
    End With
  End If

Upvotes: 3

Related Questions