Restore table column to original formula

[trepetti]

I have a table that is used to manipulate quotes containing our cost. It starts as a blank table that we paste in worksheet that we get from the vendor. Our table has additional columns that contain saved formulas, so they are applied to the new vendor data each time we start a new quote.

I want to be able to reset a particular column to it's original formula, something I can easily do with the "Restore to Calculated Column Formula" in the error checking feature.

I would love to be able to trigger that feature from within VB, but if I cannot I have written a simple module that loops row by row and inserts the formula into the specific column.

Problem is, I cannot get it to run.

The formula originally in the cell is:

=[@
[List Price]]*1-[@
[List Price]]*[@[% OffList]]

The module looks like this:

Sub OffList()
Sheets("Pricing").Select
    Dim x As Single
    Dim r_Count As Long
    Dim MyTable As ListObject
    Dim MyWorksheet As Worksheet
    Set MyWorksheet = ActiveWorkbook.Worksheets("Pricing")
    Set MyTable = MyWorksheet.ListObjects("QuoteLines")
    x = 2
    r_Count = MyTable.DataBodyRange.Rows.Count + 1
    Sheets("Pricing").Select
    Do While x <= r_Count
        Cells(x, 18).Formula = [@
[List Price]] * 1 - [@
[List Price]] * [@[% OffList]]
        x = x + 1
    Loop
End Sub

What are my options to make this work?

Thanks

 

[DryBSMT]

This has been posted for a while and I stumbled up on it while searching for a solution as well. I have figured out solution that works in my case.
My table has a hidden first row below the headers and all of our work is done starting in the next row. I keep the formulas in the hidden row and protected.
I have discovered that if the column is cleared, you can easily restore the formulas by copying and pasting the hidden cell.

In VBA I have these two lines:

Sheet1.Range("P5:P199").ClearContents
Cells(4, 17).Formula = "=IF($C4=$C3,,IF(ISBLANK($M4)=TRUE,,IF($M4=$M3,Q3,0)))" 'Cells(Row, Column)

This clears the columns and inserts the formula in the first cell which automatically fills the column with the formula.
The key is to have the column cleared of data or it won't work.

I hope this helps someone else along the way.