Inserting Excel Formula with Variable from VB2010

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
I have successfully used this code:

.Cells(intR, intC).FormulaR1C1 = "=Sum(R[-2]C:R[-1]c)" to put a Sum formula into and Worksheet.

Now I am trying to use this code:

.Cells(intR, intC).FormulaR1C1 = "=Sum(R[-intSumRowCounterRow]C:R[-1]c)"

The difference in the situations is that in the first, I KNOW I always will want to sum two rows. In the second case, the length of the column I want to sum varies, and is being counted in the variable "intSumRowCounterRow" as rows are added. The error that it is throwing is:


Any advice on how to insert the formula would be appreciated.

Question by:Buck_Beasom

Accepted Solution

VipulKadia earned 2000 total points
ID: 37004906
Try this one :
.Cells(intR, intC).FormulaR1C1 = "=Sum(R[-" & intSumRowCounterRow & "]C:R[-1]c)"

Author Closing Comment

ID: 37005681

I had to convert the integer to a string, but once I did that, concatenating the string elements as you suggested worked like a charm.


Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

After several hours of googling I could not gather any information on this topic. There are several ways of controlling the USB port connected to any storage device. The best example of that is by changing the registry value of "HKEY_LOCAL_MACHINE\S…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 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