[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 523
  • Last Modified:

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
0
David Phelops
Asked:
David Phelops
  • 2
2 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

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

Open in new window


cheers, teylyn
0
 
NorieCommented:
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

0
 
David PhelopsAuthor Commented:
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
0
 
NorieCommented:
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 [ ].
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now