VBA: formula calculation on dynamic range

[Jeffrey Lebowski]

Hi there,

I have a pretty large macro that I run to validate the accuracy of data in a file. Right now the code has 100 + formulas referencing static ranges and performing calculations. The only problem is as the file grows in size with time, these formulas will need to reference the entire data range.

What is the trick to use variable ranges within a formula?

Is this close:

 Range("B10").FormulaR1C1 = "=SUM(Range(startRow & startCol & ":" & endRow & endCol))"

 

[YasserKhalil]

Try this

Range("B10").Formula = "=SUM(" & Range(Cells(startRow, startCol), Cells(endRow, endCol)).Address & ")"

 

[jindon]

No idea about how you get startCol/endCol though.

My guess

Range("B10").Formula = "=SUM(" & cells(startRow,startCol).address & ":" & cells(endRow,endCol).address & ")"

 

[NARAYANK991]

Hi ,

My 2 cents :

Public Sub temp()
          Dim rangeParameter As Range
          Dim startRow As Long, endRow As Long
          Dim startCol As String, endCol As String

          startRow = 10
          startCol = "F"

          endRow = 77
          endCol = "J"

          Range("B10").Formula = "=SUM(" & startCol & startRow & ":" & endCol & endRow & ")"

          Set rangeParameter = Range(startCol & startRow & ":" & endCol & endRow)
          With rangeParameter
                Range("B11").Formula = "=SUM(" & .Address(1, 1) & ")"
                Range("B12").Formula = "=SUM(" & .Address(1, 0) & ")"
                Range("B13").Formula = "=SUM(" & .Address(0, 1) & ")"
                Range("B14").Formula = "=SUM(" & .Address(0, 0) & ")"
          End With
End Sub

Narayan

 

[Jeffrey Lebowski]

Narayan,

This looks promising. Can you explain what the RangeParameter does?

Thank you everyone for the input!

 

[NARAYANK991]

Hi Jeffrey ,

All that has been done is to present 2 different ways of coding the same formula ; in the first statement , the formula is coded using the row and column numbers , while in the second statement , the variable rangeparameter is a variable of type Range , which is set to the desired worksheet reference. Now , to use this in the formula , we need to include its address.

Basically , when you want to insert a formula using VBA , it has to be a string ; either this string is hard-coded into the statement , or it is derived using the Address property of a Range variable.

Narayan