0
ws2.Cells(87, col - 2).FormulaR1C1 = "=R[-5]C[0]-'Another Sheet'!F89"

When I run the above code, the result would be, "=AW83- 'Another Sheet'!'F89", which returns an error because the ' between ! and F89. I tried to use " & " but it does not work this way. Does anyone know how to solve this? Thanks in advance!!

CHsu
  • 17
  • 6

1 Answers1

0

You cannot mix R1C1 and A1-notation in a formula. If you use FormulaR1C1, you have to provide all addressen in R1C1-notation. You can use Application.ConvertFormula to convert your F89:

dim r as range
set r = ws2.Cells(87, col - 2)
r.FormulaR1C1 = "=R[-5]C[0]-" & _
      Application.ConvertFormula("'Another Sheet'!F89", xlA1, xlR1C1, , r)
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Thanks Thomas, but the result doesn't seem to refer to the correct cell - I got C113 instead of F89. Any idea why that happened? – CHsu May 16 '18 at 09:41
  • Ok I got it- relative reference. Now it works. Thank you very much! – CHsu May 16 '18 at 14:02