Set ActiveCell.FormulaR1C1 with variable

Is there a way to dynamically reference the row or column utilizing a variable?


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.
Who is Participating?
jppintoConnect With a Mentor Commented:
Did you tryed like this?

ActiveCell.FormulaR1C1 = "=SUM(R[" & r & "]C:R[-1]C)"

BryanKippAuthor Commented:
That works! Thanks!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.