<div>
<div class="content wysiwyg-content">
Is there a way to dynamically reference the row or column utilizing a variable?<br />
<br />
Example:<br />
<br />
ActiveCell.FormulaR1C1 = &quot;=SUM(R[-19]C:R[-1]C)&quot;<br />
<br />
Instead of the [-19], I need the ability to vary the number of rows based on the number of rows currently holding data. I have a variable (integer) &quot;r&quot; that currrently holds the number of the last row with data, so I'd like to be able to substitute &quot;r&quot; for &quot;-19&quot;. I have tried it using an integer and also a string, but can't get it to function.
</div>
</div>


Is there a way to dynamically reference the row or column utilizing a variable?

Example:

ActiveCell.FormulaR1C1 = "=SUM(R[-19]C:R[-1]C)"

Instead of the [-19], I need the ability to vary the number of rows based on the number of rows currently holding data. I have a variable (integer) "r" that currrently holds the number of the last row with data, so I'd like to be able to substitute "r" for "-19". I have tried it using an integer and also a string, but can't get it to function.

Set ActiveCell.FormulaR1C1 with variable

Is there a way to dynamically reference the row or column utilizing a variable?

Example:

ActiveCell.FormulaR1C1 = &quot;=SUM(R[-19]C:R[-1]C)&quot;

Instead of the [-19], I need the ability to vary the number of rows based on the number of rows currently holding data. I have a variable (integer) &quot;r&quot; that currrently holds the number of the last row with data, so I&#39;d like to be able to substitute &quot;r&quot; for &quot;-19&quot;. I have tried it using an integer and also a string, but can&#39;t get it to function.

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.