VBA Set Cell Gains Extra Quotes

[cabsta]

Hi All

When I try to set a cell's value to be a vlookup formula such as the following
Worksheets("Sheet1").Cells(1, 1).Value = "=VLOOKUP(RC[1], Sheet2" & Chr(33) & "B8:G500, 2,false)"

the value that gets entered into the cell is the following:
=VLOOKUP(B1, Sheet2!'B8':'G500', 2,FALSE)

rather than the desired
=VLOOKUP(B1, Sheet2!B8:G500, 2,FALSE)

Does anybody know where these quotes come from and how I can set a cell to be a vlookup field without these quotes?

[romperstomper]

Don’t mix r1c1 references with a1 style references - use one or the other. I’m not sure why you need to use Chr there at all.

[TMS]

Different ways to get what you want:

Worksheets("Sheet1").Cells(1, 1).FormulaR1C1 = "=VLOOKUP(RC[1], Sheet2!R8C2:R500C7, 2,FALSE)"
Worksheets("Sheet1").Cells(1, 1).FormulaR1C1 = "=VLOOKUP(RC[1], Sheet2!R[7]C[1]:R[499]C[6], 2,FALSE)"
Worksheets("Sheet1").Cells(1, 1).Formula = "=VLOOKUP(B1, Sheet2!B8:G500, 2,FALSE)"

Worksheets("Sheet1").Range("A1:A10").FormulaR1C1 = "=VLOOKUP(RC[1], Sheet2!R8C2:R500C7, 2,FALSE)"
Worksheets("Sheet1").Range("A1:A10").FormulaR1C1 = "=VLOOKUP(RC[1], Sheet2!R8C[1]:R500C[6], 2,FALSE)"
Worksheets("Sheet1").Range("A1:A10").Formula = "=VLOOKUP(B1, Sheet2!$B$8:$G$500, 2,FALSE)"