0

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

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Patrick.H
  • 535
  • 3
  • 6
  • 21
  • What is `lookupWS`? Was that supposed to be `lookupRange`? You declare a variable that you don't use but then use a variable that you don't declare. Please give a [mcve]. – John Coleman Mar 05 '18 at 15:58
  • 2
    Does the lookup value exist? See https://stackoverflow.com/questions/19280477/unable-to-get-the-vlookup-property-of-the-worksheetfunction-class-error – QHarr Mar 05 '18 at 15:59
  • Without any *data* we really can't help you – CallumDA Mar 05 '18 at 16:10
  • @John Coleman lookupRange is defined in the code, you are right lookupWS isnt, my fault. Ist is the worksheet – Patrick.H Mar 05 '18 at 16:11
  • @QHarr omg you were right! I will mark your answer as correct as soon as I am on PC again – Patrick.H Mar 05 '18 at 16:16

1 Answers1

4

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
CallumDA
  • 12,025
  • 6
  • 30
  • 52