Using variable for sheet name in excel VBA VLookup

Question

Private Sub UpdateBoxes()
    Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
    Dim sheet As String
    If Range("C1") = "some string" Then
        sheet = "SomeSheet"
    End If

    Range("B6").Value = Sheets("Spreadsheet Ctrl").Range("B7") 'Sets title of Box 1 based on Spreadsheet Ctrl
    Range("G6").Value = Sheets("Spreadsheet Ctrl").Range("C7") 'Sets title of Box 2 based on Spreadsheet Ctrl
    Range("B11").Value = Sheets("Spreadsheet Ctrl").Range("D7") 'Sets title of Box 3 based on Spreadsheet Ctrl
    Range("G11").Value = Sheets("Spreadsheet Ctrl").Range("E7") 'Sets title of Box 4 based on Spreadsheet Ctrl
    Range("B16").Value = Sheets("Spreadsheet Ctrl").Range("F7") 'Sets title of Box 5 based on Spreadsheet Ctrl
    Range("G16").Value = Sheets("Spreadsheet Ctrl").Range("G7") 'Sets title of Box 6 based on Spreadsheet Ctrl

    Range("C7").Value = wsFunc.VLookup(B6,'" & sheet & '"!A1:G5,3,)" 'Vlookup for "Current Revision
End Sub

The variable "sheet" will eventually change based on a nested if. It should then be passed to the last line of code before End Sub. I am getting a compile error that states "Expected: expression", and it highlights the first tick in '" & sheet & '".

Tim Williams · Accepted Answer · 2013-10-07 23:21:09Z

1

Something like:

Range("C7").Value = wsFunc.VLookup(ActiveSheet.Range("B6"), _
                                   Sheets(sheet).Range("A1:G5"),3,False)

edited Oct 7, 2013 at 23:21

answered Oct 7, 2013 at 23:09

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Drexl Over a year ago

Range("C7").Value = wsFunc.VLookup(ActiveSheet.Range("B6"), Sheets(sheet).Range("A1:G5"), 3,) 'Vlookup for "Current Revision"

This reults in a compile error: "Expect: Expression" which highlights the last close parenthesis.

Drexl Over a year ago

You're awesome. That did it. Thanks! ^_^ I didn't realize that was explicitly required since it isn't in the standard formula.

Collectives™ on Stack Overflow

Using variable for sheet name in excel VBA VLookup

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related