VBA FormulaR1C1 w/ standard formula

  • I need to place a formula in a cell which contains both a standard ( "=Page2!E5") and R1C1 formula but I am have trouble with the VBA code syntax.

    The formula is placed in page1, column G in a row determined by a row variable (ExRow). It needs to find the value in page2 cell E5, and divide it by the value in page1, column D of the same row.

    So the VBA code would look something like this:

    Worksheets("Page1").Cells(ExRow + 5, 7).FormulaR1C1 = "=Page2!E5/RC[-3]"

    However, the result for ExRow=54 is =Region!'D5'/E54 when it should be =Region!D5/E54

    Any suggestions? Thanks!

  • Re: VBA FormulaR1C1 w/ standard formula

    Hi oldman33,
    Nice to meet you.
    This works.

    ActiveCell.FormulaR1C1 = "=Sheet1!" & Range("e5").Address(False, False, xlR1C1) & "/rc[3]"

    Regards, junho

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!