Inserting Excel Formula with Variable from VB2010

Posted on 2011-10-20
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
    LVL 8

    Accepted Solution

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

    Author Closing Comment


    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (…
    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.…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now