View original (French)

Formula R1C1 with variable

anakin_74 Posted messages 43 Status Membre -  
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   -

Hello,

Sorry if my question seems simplistic, but I'm just tinkering a bit with VB Excel.

I recorded a macro that selects cell A50 where I copy and paste the formula from A14.

This gives me:

Range("A50").select

ActiveCell.FormulaR1C1 = "=R[-36]C"

My issue is that I reformat (adding n rows) between A14 and A50 before using the formula function, so I no longer have the correct cell to copy from.

Is it possible to use a variable instead of "[-36]"?

Or is there a trick with another "formula"?

Thanks in advance.

2 réponses

Réponse 1 / 2
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 587
 

Hello,

The code does not copy the formula from A14 to A50, it puts a link to A14 in A50.

So it's not always to A50, but always starting from A14?

Maybe simply

ActiveCell.FormulaR1C1 = "=A14"
0
Réponse 2 / 2
anakin_74 Posted messages 43 Status Membre
 

I used the formula and it gives me #NAME?

I will clarify my expectation.

I run a macro that formats my document according to my line needs.

For now, the cells are empty or have formulas.

In cell A14, I have a formula: =A13 +1

If I don't add any lines, I want cell A40 (formula): =A14

If I add 10 lines to the document, I want cell A50 (formula): =A14

I hope I have been clear this time.
0
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   1 587
  
ActiveCell.Formula = "=A14"
0