asked on

Entering a variable range in a formula using VBA

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))"
    ActiveCell.Select

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

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Phelops

ASKER

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.

David

Norie

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.

=IF($F$8="",X7,SUM(X7:X853))

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

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