CODEWITHSUNDEEP
vbaexcelvlookup
SMORF
SMORF

Reputation: 499

Excel VBA Using cell value in vlookup formula

I am trying to create a vlookup to a worksheet that has the same name as the cell value in my active sheet.

I created the following thinking I could use the 'str' in my vlookup formula but, I get 'Run-time error '1004': Application-defined or object-defined error'

Sub copy()

    Dim LastRow As Long
    Dim str As String

    str = Cells(1, 5).Value

    With Sheets("Overview")
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

        With .Range("E2:E" & LastRow)
            .Formula = "=VLOOKUP(B2, & str &!B:F,5,FALSE)"
        End With

    End With

End Sub

Can anyone see what I am doing wrong?

Upvotes: 0

Views: 2372

Answers (1)

Jordan
Jordan

Reputation: 4514

You've defined str in VBA but referred to it in the formula without closing off the quotation marks, try this:

Sub copy()

Dim LastRow As Long
Dim str As String

str = Cells(1, 5).Value

With Sheets("Overview")
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    With .Range("E2:E" & LastRow)
        .Formula = "=VLOOKUP(B2," & str & "!B:F,5,FALSE)"
    End With

End With

End Sub

Upvotes: 2

Related Questions