Remove single quote VBA function

Question

To simplify, I have two sheets in an Excel workbook.

If I write =sheet2!R3C2 in one of the cells of sheet1, the cell takes the value of the cell R3C2 of sheet2 correctly. I would like to generalize that for many rows in VBA, so my code is:

row = XX a long 

temp = "=sheet2!R" & row - 1 & "C2"

Range("c" & row).Value = temp

But when I use this code, the value of the cell(row,3) is =sheet2!'RXXC2'

How can I remove the single quotes ??

Answer 1

You want to set the formula, not the value:

Range("c"&row).FormulaR1C1 = temp

Answer 2

try to write Range("c" & row).FormulaR1C1=temp

Answer 3

Range("C" & row).Formula = temp

would produce the correct formula in your cell.

What you should consider doing instead of looping is

Range("A1").Formula = "=Sheet2!$B1"
Range("A1").Resize(100, 1).Formula = Range("A1").Formula

The first line inserts a formula =Sheet2!$B1 in cell A1 of your active sheet. The $ dollar sign assures that the column will not be incremented (same applies with numbers)

Then the second line duplicates the formula across 100 rows down from A1 replacing the number after the B column

So now

A1 =Sheet2!B1
A2 =Sheet2!B2
A3 =Sheet2!B3
... 

Also, it's a bit unclear what you're trying to actually do so consider another option which is saving the value of formula into another range using the Evaluate() function

Range("c" & row).Value = Evaluate(temp) Or Range("C" & Row).Value = [temp]