• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1597
  • Last Modified:

Set ActiveCell.FormulaR1C1 with variable

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.
0
BryanKipp
Asked:
BryanKipp
1 Solution
 
jppintoCommented:
Did you tryed like this?

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

0
 
BryanKippAuthor Commented:
That works! Thanks!!
0
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now