EXCEL / VBA - Dynamically insert formulas into cells

0
votes

I have a table and the number of rows it has is unknown. Every time the user enters an item into the column A of this table, I want the other columns in the table to dynamically get populated with formulas that reference the item just entered in column A.

I don't want to simply copy the formula into columns B,C,D etc all the way down my sheet because the number of rows in the sheet is unknown.

Is there any efficient way to do this?

My attempts of writing code in the onCalculate and onChange events in VBA seem very inefficient or result in eternal loops.

2
exceleventsvba
Got it.... Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 Then Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Sheet2.Cells(Target.Row, 5).Formula = "=XXX(""XXX""," & Target.Address & " ,E5)" Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic End If End Sub - stan
You can self answer if you'd like. - Lance Roberts

2 Answers

2
votes

If you are using Excel 2007 or Excel 2010, defining your list range as a List will do that automatically !
The best macro is no macro

1
votes

Here's a sample that will fill in the blank formulas in B,C and D whenever your selection changes:

In ThisWorkbook:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim oSh As Worksheet, oUsed As Range, oRng As Range

    Set oSh = Sh
    Set oUsed = oSh.UsedRange.Columns("B")

    'only add to blank formulas
    Set oRng = oUsed.Find("", LookIn:=xlFormulas)

    Do While Not oRng Is Nothing
        'B formula
        oRng.Offset(, 0).FormulaR1C1 = "=RC[-1] + 100"
        'C formula
        oRng.Offset(, 1).FormulaR1C1 = "=RC[-1] + 100"
        'D formula
        oRng.Offset(, 2).FormulaR1C1 = "=RC[-1] + 100"
        Set oRng = oUsed.Find("", oRng, xlFormulas)
    Loop
End Sub

This works on every worksheet in the workbook, so you may want to test which sheet you're in.