CODEWITHSUNDEEP
excelvbams-office
H_Braun
H_Braun

Reputation: 85

Loop through every table row and insert different formula

i have a table with predefined rows and columns. I want to loop through every row and put a different formula into each cell. The cell should show data from a different worksheet. This step is to be repeated for every column.

                                      Repeat:
+--------+--------+-----+---------+  1 || 2 ->
|Column 1|Column 2| ... | Column n|  |
+--------+--------+-----+---------+  |
|row 1   | row 1  | ... | row 1   |  |
+--------+--------+-----+---------+  v
|                ...              |
+--------+--------+-----+---------+
|row n   | row n  | ... | row n   |
+--------+--------+-----+---------+

I tried using a for loop, which runs until the last row and places the formula with the .FormulaLocal method into the current cell.

For i = 1 To UBound(bmPos)
        curTable.DataBodyRange.Cells(i, curTable.ListColumns("Column1").Index).FormulaLocal = _
                "=Table1!" & wksBM.Cells(bmPos(i), rngDate.Column).Address
        [...]
        curTable.DataBodyRange.Cells(i, curTable.ListColumns("ColumnN").Index).FormulaLocal = _
                "=Table1!" & wksBM.Cells(bmPos(i), rngCustomer.Column).Address
Next i

The expected result is that each cell i contains a formula like this: =Table1!$A$1 ... =Table1!$A$ i

What i see when i step through my code with the debugger is, that in the very first iteration, all rows get the same value. So after the last iteration, all rows contain the formula =Table1!$A$ i

Infact, when I replace the whole formula with just i, I get the right iteration step in the cell.

Upvotes: 0

Views: 160

Answers (1)

horst
horst

Reputation: 713

The Excel Table is autofilling the whole column with the last Formula you entered on any Row.

To Prevent this, put Application.AutoCorrect.AutoFillFormulasInLists = False in the beginning of your Sub.

Upvotes: 1

Related Questions