Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Set ActiveCell.FormulaR1C1 with variable

Posted on 2010-11-11
2
Medium Priority
?
1,393 Views
Last Modified: 2012-05-10
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
Comment
Question by:BryanKipp
2 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 34114210
Did you tryed like this?

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

0
 

Author Closing Comment

by:BryanKipp
ID: 34114240
That works! Thanks!!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question