Search code examples
excelvbareferenceexcel-r1c1-notation

VBA R1C1 formula calculation with cell in another worksheet

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!!

Solution

  • 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)