Excel VBA: using R1C1 format, “Application defined

问题:

I am trying to create a formula that references other cells, but I keep getting this "Application defined or object defined error". I use R1C1 convention instead of Offset.

My Code:

Note: This code could be anywhere, I just need it to work. Also, I don't use the code like this. It is used in a reporting tool, but this is just similar code (to what I actually use) sufficient enough to show my problem

Cells(1, 1).Value = "5/1/2014 6:30"
soiDate = "$A$1"
Cells(10, 6).Value = "6/5/2014 14:12"
Cells(10, 10).Formula = "=(R[0]C[-4]-" & soiDate & ")*24" 'Error Occurs Here 

UPDATE:

The following does not work either:

Cells(10, 10).FormulaR1C1 = "=(R[0]C[-4]-" & soiDate & ")*24"

回答1:

Try this:

Cells(10, 10).FormulaR1C1 = _
    "=(R[0]C[-4]-" & Range(soiDate).Address(, , xlR1C1) & ")*24"

It errors out since you kinda mixed up R1C1 reference to A1.
Just be consistent, you can use Range Object address property to convert reference. HTH.