Entering a variable range in a formula using VBA

Posted on 2012-09-21
Last Modified: 2012-09-21
I recorded entering a formula, and added explanation:
'Select the field to enter the formula
'Insert formula to calculate totals from job table:
'If there is only one record, use the record value, otherwise sum the whole column.
    Range("X2").Select                    'R8C6 = Cell("F8") -  job no.
    ActiveCell.FormulaR1C1 = "=IF(R8C6="""",R[5]C,SUM(R[5]C:R[851]C))"

Open in new window

There are obviously 851 records in this table.

How do I code for  different record numbers in the table?

Thanks very much
Question by:David Phelops
    LVL 50

    Accepted Solution


    maybe it's just me, but I find the FormulaR1C1 thing quite confusing. You can use the Formula method instead, which is a bit more user friendly.

    Define a variable and populate it with the last row of the data in column X. Then use that row number in the formula, like this:

    Sub test()
    Dim lastRow As Long
    'Select the field to enter the formula
    'Insert formula to calculate totals from job table:
    'If there is only one record, use the record value, otherwise sum the whole column.
        lastRow = Cells(Rows.Count, "X").End(xlUp).Row
        Range("X2").Select                    'R8C6 = Cell("F8") -  job no.
        ActiveCell.Formula = "=IF($F$8="""",X7,SUM(X7:X" & lastRow & "))"
    End Sub

    Open in new window

    cheers, teylyn
    LVL 33

    Assisted Solution

    Which column can be used to get the no of records?

    It needs to be a column that has data down to the last row.

    If it was column A the code might look something like this.
     LastRow = Range("A" & Rows.Count).End(xlUp).Row
     Range("X2").FormulaR1C1 = "=IF(R8C6="""",R[5]C,SUM(R[5]C:R" & LastRow & "C))"

    Open in new window


    Author Comment

    by:David Phelops
    Perfect... thank you both... it's one of those "Slap-forehead-and-shout-OF-COURSE" moments.
    Much obliged!

    Thanks teylyn,  as well, for the added detail of the variable declaration.

    LVL 33

    Expert Comment

    The R1C1 notation  is quite confusing here.

    It might appear there are 851 records but the 851 in the formula is a relative reference.

    The formula, in A1 notation, that the original code produces is this.


    There are actually 847 records/rows in the range X7:X853.

    To use absolute references in R1C1 you remove the [ ].

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    760 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

    10 Experts available now in Live!

    Get 1:1 Help Now