FormulaR1C1 to Write a Formula Question

Give this a try...

Please Login or Register  to view this content.

In VBA .FormulaR1C1 when using relative reference, must be relative to the cell where the formula is being entered to.

So for H2 referencing G2... since G column is 1 column to left of H, but remaining in same row.

Please Login or Register  to view this content.

You are very welcome. We are happy to help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

It is difficult to see what difficulty you are having. jeffreybrown's suggestion might be the final solution for what you are after, so my post may be superfluous (ignore, in that case).

The main problem I see is that you are not understanding how relative references work in R1C1 notation. This provides a good explanation of R1C1 referencing (along with other reference types: https://support.office.com/en-us/art...es_in_formulas ).

R[x]C[y] references are relative references that show how far to go from the formula cell to retrieve data. R[2]C[7] in H2 (R2C8) means "go down +2 rows and to the right +7 columns" which is R4C15 or O4. To get "same row 1 column to the left (-1 column to the right)" you use R[0]C[-1]. The [0] is optional, and Excel will enter it as RC[-1].

@MrShorty,

I think the original problem could have been a missing equal sign.

1) When I use, Range("H2").Formula = "G2*1.01" it calculates this correctly, but it does not leave the formula in the cell.