VBA enter R1C1 formula in cell including string

[fbohlandt]

Hi all

I have a simple VBA script that is writing formulas to Excel. In Excel, the formula will look something like this:

="'"&A3&"'"

Note the high commas bracketing the cell reference.

I am not sure what to do in VBA to combine cell reference and string. Here is what I have (which is not really working):

Sub Quote()

    ThisWorkbook.ActiveSheet.Range("H3").FormulaR1C1 = Chr(39) & "=R3C1" & Chr(39)

End Sub

Ideally, I would like to keep the R1C1 cell reference style.

Thanks in advance

 

[jasonb75]

Based on your example it looks as simple as adding the 'high commas' to the R1C1 reference in the code, although it is not entirely clear what the final outcome should be.

ThisWorkbook.ActiveSheet.Range("H3").FormulaR1C1 = Chr(39) & "='R3C1'" & Chr(39)

 

[GWteB]

Would suggest to switch to the Formula property, as in ...

ThisWorkbook.ActiveSheet.Range("H3").Formula = "=""'""&A3&""'"""