VBA Formula R1C1 Code

Question

I am trying to put the formula in a range using the code below, but it is throwing error. Can someone help?

shtRpt. Range("rng"). FormulaR1C1 = " =OFFSET(INDIRECT(" Sheet1! A1"), Match(RC[30],INDIRECT("Sheet1!A:A),0)-1,8)"

Its getting highlighted in red. I have used R1C1 before and have not come across such a problem. Please help?!

you're also mixing `A1` and `R1C1` referencing. Use one not both — Tom, Sep 19 '17 at 11:13

score 0 · Accepted Answer · answered Sep 19 '17 at 07:24

0

You haven't escaped your double quotes. Something like:

shtRpt. Range("rng"). FormulaR1C1 = " =OFFSET(INDIRECT("" Sheet1! A1""), Match(RC[30],INDIRECT(""Sheet1!A:A""),0)-1,8)"

answered Sep 19 '17 at 07:24

serakfalcon

3,501
1
22
33

score 0 · Answer 2 · answered Sep 19 '17 at 08:56

Do the following:

Make sure that your formula in Excel works.
Select the cell with the formula.
Run the code below.
Take a look at the immediate window and compare the values with what you have.
Adjust the values.

Public Sub PrintMeUsefulFormula()

    Dim strFormula  As String
    Dim strParenth  As String

    strParenth = """"

    strFormula = Selection.FormulaR1C1
    strFormula = Replace(strFormula, """", """""")

    strFormula = strParenth & strFormula & strParenth
    Debug.Print strFormula

End Sub

In general, looking at your formula, you have one additional space here " =OFFSET(INDIRECT(" . It should be like this "=OFFSET(INDIRECT("

VBA Formula R1C1 Code

2 Answers2