I am trying to preserve formulas in tables in case they are unintentionally overwritten / deleted. I have two tables. The first table is a list of all formulas used in all tables in the workbook. Tbl_A has two columns. Tbl_A Column1 has the range that the formula relates to (ex. Tbl_B.DataBodyRange.Cells(1, 2) .) Tbl_A Column2 has the formula that should be used for Tbl_B.DataBodyRange.Cells(1, 2) entered as text (first character in formula is '). I simply want the code to loop through all Tbl_A Column1 cells, and use the location from that cell to go to the location that it refers to and insert the formula from Tbl_A Column1.Offset(0, 1). I have tried using
Set Rng = Tbl_a.DataBodyRange.Cells(i, 1).Value
instead of
Set Rng = Tbl_a.DataBodyRange.Cells(i, 1)
, but it throws a Run time error '424' Object Required.
I cannot use absolute cell references as the tables can be moved from one area of the worksheet to another. Any help greatly appreciated. Sample file attached.
Option Explicit
Sub SetFormulas()
Dim Sht01 As Worksheet
Dim Tbl_a As ListObject
Dim Tbl_b As ListObject
Dim i As Integer
Dim LastRow As Long
Dim Rng As Range
Set Sht01 = Sheet1
Set Tbl_a = Sht01.ListObjects("Tbl_A")
Set Tbl_b = Sht01.ListObjects("Tbl_B")
ThisWorkbook.Activate
Sht01.Activate
LastRow = Tbl_a.ListRows.Count
i = 1
For i = i To LastRow
Set Rng = Tbl_a.DataBodyRange.Cells(i, 1)
Rng.Formula = Tbl_a.DataBodyRange.Cells(i, 1).Offset(0, 1)
Next i
End Sub
Capture.JPG
Capture1.JPG
Capture2.JPG
Bookmarks