Set Formula of Specific Cell in Table

Question

How do you set the formula of a specific cell in a table? I have a table (tblPO) with columns Unit Price, List Price and Disc.. I need to loop through the rows of the table checking whether the Disc. column of each row contains a value, and if it does, the formula of the Unit Price of that row needs to be set to the List Price minus the Disc. Here’s what I have so far to loop through the table and check whether the Disc. has a value or not:

For I = 1 To Range("tblPO[#Data]").Rows.Count
  If [tblPO].Cells(I, [tblPO[Disc.]].Column) <> vbNullString Then
    <Unit Price cell formula> = "=[@[List Price]]*(1-[@[Disc.]])"
  End If
Next I

you can just add a column to the data table and set the formula for the column once and it will apply to every row in the table ... see [this](http://stackoverflow.com/questions/17549885/insert-new-column-in-table-excel-vba) for adding column and [this](http://stackoverflow.com/questions/13753563/add-modify-delete-calculated-column-formula-in-excel-listobject-table-via-vba) for adding formula — Scott Holtzman, Jul 13 '16 at 21:12
Read my question @ScottHoltzman. The formula only needs to be applied to certain rows (promotion items). Not all items in the table are on promotion, which is why I need to loop through every row in the table and check its `Disc.` column for a value to determine whether it is a promotion item. If the item is on promotion, the `Disc.` column contains the discount that needs to be applied to its `List Price` of which the new `Unit Price` is calculated. — Reginald Greyling, Jul 13 '16 at 21:33
I did read the question. You can apply an `IF` statement into the formula and it will appear on each row `IF(ISBLANK([DISC]),"",[DISC]*[LIST PRICE]) ... *(psuedo code)*` — Scott Holtzman, Jul 14 '16 at 13:04

Set Formula of Specific Cell in Table

0 Answers0