Excel VBA Using cell value in vlookup formula

Question

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?

@ScottCraner This is a simple syntax error: should we close this question as of no much merit? Otherwise, you can post your answer and I will upvote it (btw, the other posted answer actually contains your solution, but you have posted it first, so the credit should go to you). Best regards, — Alexander Bell
@AlexBell we posted at nearly the same time, I almost deleted my comment. I am okay with giving the credit for the correct answer to Jordan. If I were to post an answer it would be identical to his. But thank you. I doubt this would get enough votes to close. — Scott Craner
@ScottCraner Well, you've posted your comment a minute before that other (identical) answer, so kudos is going to you, but if you decide so, then it's OK to keep this question open w/given answer. Best regards to you and other folks on this thread, — Alexander Bell

Jordan Jordan · Accepted Answer · 2016-03-24T14:25:55

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

Excel VBA Using cell value in vlookup formula

1 Answers