Results 1 to 4 of 4

Refer to cell location in table and insert related formula in cell location

Threaded View

  1. #1
    Registered User
    Join Date
    07-31-2014
    Location
    Texas
    MS-Off Ver
    Microsoft Office Professional Plus 2021
    Posts
    49

    Refer to cell location in table and insert related formula in cell location

    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
    Attached Files Attached Files
    Last edited by LITOA; 05-08-2021 at 05:44 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Rename Files and move them to a cell related location
    By Chrysanthiiiiii in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2019, 11:55 AM
  2. [SOLVED] Formula to refer the location given in another cell as hyperlink
    By akhileshgs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-02-2016, 08:27 AM
  3. [SOLVED] insert formula on cell dependent on location of another cell
    By ghynes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-10-2015, 05:55 AM
  4. [SOLVED] [Help]macro to open file browser, to select a location, and save the location to a cell
    By zhuleijia in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-06-2013, 09:56 AM
  5. how to insert text as cell location in formula
    By squidy37 in forum Excel General
    Replies: 3
    Last Post: 12-15-2011, 03:02 PM
  6. Ways to refer a cell location
    By SpringLily in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2011, 04:02 AM
  7. Refer to a specific cell location no matter what!
    By brucemc in forum Excel General
    Replies: 4
    Last Post: 07-23-2007, 12:12 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1