I have the below code that returns a value with single quotes. I need to add more characters beyond the single quotes, but am not able to get anything more without an error.

Sub BandVLook()
Dim LastRow As Long, cell As Range
Dim LookupRange As Range, MyStr As String

Set LookupRange = Sheets("Sheet1").Range("Q2:S500")
LastRow = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2").Range("D1:D" & LastRow)
    .NumberFormat = "General"
    .FormulaR1C1 = "=""''"" & VLOOKUP(RC[-2],Sheet1!C17:C19,3,FALSE) & ""'"""
    .Value = .Value
End With

End Sub
I am usually able to use the various ascii characters (Chr(xx)) but those aren't working either. Ugh.

The value I'm getting is 'TowerOfPower'.

The value I need is:

("") to ("TowerOfPower")

Thanks for your help!