CODEWITHSUNDEEP
vbaexcelvlookup
Patrick.H
Patrick.H

Reputation: 575

VLookUp not working - Property could not be assigned

My problem is, that when using the VlookUp I do get the Error:

The VLookup-Property of the WorksheetFunction-Object could not be assigned

        ' LookUp missing Data
        Dim lookupRange As Range
        Set lookupRange = Sheets("Kundenlisten HLK 2018").Range("A1:Y10354")

        ' Fill in Companyname
        Dim tmp As String
        tmp = Values(0)
        tmp = TrueTrim(tmp)
        testing.Cells(8, counter) = Application.WorksheetFunction.VLookup(tmp, lookupWS.Range("A2:Y10354"), 2, False)

        Values = None
        counter = counter + 1

lookupWS is the Name of the Worksheet As you can see the table I am trying to lookup is filled with values from A to Y. The first column is the key I am trying to look up, but then the error from above occurs. The tmp variable is a String with a unique ID to look up the missing values, while the "2" is supposed to be the company name in the second column of the Range.

I looked up on the docs, but the types and everything are fine, I even checked while debugging.

testing.Cells(8, counter) can't be the source of the problem aswell, since I am using it before like this

testing.Cells(28, counter) = Left(mail.ReceivedTime, 10)

and it works

Upvotes: 0

Views: 110

Answers (1)

CallumDA
CallumDA

Reputation: 12113

It's difficult to know what the problem is without any data, but here's something to help you in the right direction.

It's better to use Find and Offset than WorksheetFunction.Vlookup in VBA

Something like this gives you exactly the same result, but you have much more control:

Sub Test()
    Dim valueFound As Range

    Set valueFound = lookupWS.Range("A2:A10354").Find(What:="Something", lookat:=xlWhole) 'xlWhole is equivalent to FALSE in VLOOKUP
    If valueFound Is Nothing Then
        MsgBox "Nothing found"
    Else
        MsgBox valueFound.Offset(0, 1) 'offsetting by 1 is equivalent to using 2 in a VLOOKUP
    End If
End Sub

Upvotes: 4

Related Questions