CODEWITHSUNDEEP
excelvbaexcel-formula
Dodilei
Dodilei

Reputation: 188

Why is a column filled automatically when using VBA?

I'm trying to fill one cell with a 'FormulaR1C1', but the result is the entire column filled.

I have a table-like sheet, with a header, and some fields to be filled automatically in the rows used. To achieve this, I have:

Sheet1.Cells(R, 6).FormulaR1C1 = "=R[0]C[-1] * R[0]C[-2]"

Where 'R' is the row needed. (This line updates column "F".)

Somehow this line fills all the cells in that column with the Formula, only leaving the header unchanged.

I can reproduce that without running my code, using that line on the immediate-window. The same for every column I try.

The only time that doesn't happen is when there's more data in the column, then it works correctly, filling only the row asked.

Upvotes: 0

Views: 613

Answers (2)

Dy.Lee
Dy.Lee

Reputation: 7567

Try,

Dim Ws As Worksheet
Set Ws = Sheets(1)

With Ws
    r = .Range("e" & .Rows.Count).End(xlUp).Row
    .Range("f2", "f" & r).FormulaR1C1 = "=R[0]C[-1] * R[0]C[-2]"
    'To get the non-formula value, run below
    '.Range("f2", "f" & r).Value = .Range("f2", "f" & r).Value
End With

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19782

I have a table-like sheet

If it's an actual table in a sheet and not a table-like sheet then this should work....

Manually (in Excel 365) you can go to File > Options > Proofing > AutoCorrect Options > Fill formulas in tables to create calculated columns.

In VBA before you add the formula use
Application.AutoCorrect.AutoFillFormulasInLists = False

Adds a formula to row 3, column 2 of Table1:

Sub test()

    Application.AutoCorrect.AutoFillFormulasInLists = False
    Sheet1.ListObjects("Table1").ListRows(3).Range(2).Formula = "=1"
    Application.AutoCorrect.AutoFillFormulasInLists = True
    
End Sub

Upvotes: 1

Related Questions