title: Using variable for sheet name in excel VBA VLookup
body: <pre><code>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,'" &amp; sheet &amp; '"!A1:G5,3,)" 'Vlookup for "Current Revision
  End Sub
  </code></pre>
  
  <p>The variable "sheet" will eventually change based on a nested if. It should then be passed to the last line of code before <code>End Sub</code>. I am getting a compile error that states "Expected: expression", and it highlights the first tick in <code>'" &amp; sheet &amp; '"</code>.</p>