Logicwurks, LLC

Dynamically Load Formulas Into Cells

Frequently a developer needs to insert formulas into a VBA generated worksheet. If you are loading data into a target worksheet, sometimes the target worksheet can be pre-formatted with formulas. But in many cases - especially when the number of rows vary - you should insert formulas at the time the target worksheet is being constructed. There are basically four ways to do this as the following examples illustrate:

Insert a formula using R1C1 notation with no absolute row or column references using offsets:
Cells(2, 2).FormulaR1C1 = "=RC[-1] * R[-1]C"
The resulting formula when viewed in the worksheet is: =A2 * B1
Insert a formula using R1C1 notation with absolute row or column references:
Cells(2, 2).FormulaR1C1 = "=RC1 * R1C"
The resulting formula when viewed in the worksheet is: =$A2 * B$1
Insert a formula using standard Column/Row references with no absolutes:
Cells(2, 2).Formula = "=A2*B1"
Insert a formula using standard Column/Row references with absolute row/column references:
Cells(2, 2).Formula = "=$A2*B$1"

The code snippet below illustrates how you can dynamically specify the formula string based on variables. This code is not a complete program, but an illustration from sections of a working Excel program that builds formulas using variables.

Program Code

' ********************************************************
' Create Grand Total Less Miscellaneous
' ********************************************************
intSummaryColumn = intSummaryColumn + 1
Cells(3, intSummaryColumn).Value = "Grand Total Less Misc"
Cells(4, intSummaryColumn).FormulaR1C1 = "=RC[-1]-RC[-2]"

' ********************************************************
' Create Percentages
' ********************************************************
For i = 14 To 18
    For j = 2 To intSummaryColumn - 3
        Cells(i, j).NumberFormat = "0.00%"
        Select Case i
            Case 14
                Cells(i, j).FormulaR1C1 = "=R[-10]C/R[-10]C[" & intSummaryColumn - j & "]"
            Case 15
                Cells(i, j).FormulaR1C1 = "=(R[-10]C+R[-9]C)/" & _
                                            "(R[-10]C[" & intSummaryColumn - j & "]+" & _
                                             "R[-9]C[" & intSummaryColumn - j & "])"
            Case Else
                Cells(i, j).FormulaR1C1 = "=R[-9]C/R[-9]C[" & intSummaryColumn - j & "]"
        End Select
    Next j
Next i

' ********************************************************
' Sum The Percentages
' ********************************************************
For i = 14 To 18
    Cells(i, intSummaryColumn - 1).FormulaR1C1 = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]"
Next i

' ********************************************************
' Quickbooks Entry Formulas 300 Sales
' ********************************************************
For i = 2 To intSummaryColumn - 3
    Cells(26, i).FormulaR1C1 = "=R[-5]C[" & 2 - i & "]*" & "R[-12]C"
Next i

' ********************************************************
' Quickbooks Entry Formulas 310 Shipping Income
' ********************************************************
For i = 2 To intSummaryColumn - 3
    Cells(27, i).FormulaR1C1 = "=R[-5]C[" & 2 - i & "]*" & "R[-13]C"
Next i

' ********************************************************
' Quickbooks Entry Formulas 350 Sales Deductions
' ********************************************************
For i = 2 To intSummaryColumn - 3
    Cells(28, i).FormulaR1C1 = "=R[-5]C[" & 2 - i & "]*" & "R[-13]C"
Next i

' ********************************************************
' Sum The Quickbooks Entries
' ********************************************************
For i = 26 To 28
    Cells(i, intSummaryColumn - 1).FormulaR1C1 = "=RC[-5]+RC[-4]+RC[-3]+RC[-2]"
Next i

Links To Excel Code Examples

Links To Access Code Examples

Dynamically Load Formulas Into Cells

Program Code