Vba Excel Vlookup Search value is String

Question

The code below doesn't work

run time error 1004

VlookSrc (Dim As String in VBA) is the value we look for in Range("KV5:KW105673"). It's a combination of different cells depending on c and d, and the string /DIR/ in between. In the local window it is possible to see that Excel recognizes the correct string. We use the IfError Function because there won't always be value found. The run error 1004 comes with the worksheetfunction.

    If Cells(3, d) = Cells(c, 33) Then
        VlookSrc = Cells(c, 48).Value & "/DIR/" & Cells(4, d).Value
       Cells(c, d) = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(VlookSrc, Range(Cells(5, 308), Cells(105673, 309)), 2, False), 0)
    Else
        Cells(c, d) = 0
    End If

Now we found out that the problem was the cell format of the dates.But we still have the problem with the lookup values where no value is found. How is it possible to use iferror worksheetfunction? — Masterarbeit2

Gary's Student Gary's Student · Accepted Answer · 2017-11-08T13:02:47

You can't use IfError() like that in VBA, instead:

Sub luxation()
    Dim v
    With Application.WorksheetFunction
        v = 0
        On Error Resume Next
            Cells(c, d) = .VLookup(VlookSrc, Range(Cells(5, 308), Cells(105673, 309)), 2, False)
        On Error GoTo 0
    End With
End Sub

Vba Excel Vlookup Search value is String

2 Answers