Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Loop through data and create formulas

  1. #11
    New Member
    Join Date
    May 2019
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through data and create formulas

    Thank you! I'll check it out asap.

  2. #12
    New Member
    Join Date
    May 2019
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through data and create formulas

    Thanks again. Works great...until I try to move it. I'm going to be moving columns around in the table and move the table itself in the sheet. So, I need to be able to adjust the code. Normally, this is easy peasy. But when I tried with this code when adjusting the table 1 row and 1 col, it refers to a cell just one cell right of what it should be referring to. Can't find a solution yet. Below is the adjusted code. Thanks again.

    Code:
    Sub Macro1adj()  
      Dim Obj As Object, t As String, i As Long, col As Long, n As String
      
      Application.AutoCorrect.AutoFillFormulasInLists = False
      
      For i = 3 To 5
      Set Obj = Range("B" & i).ListObject
      If Not Obj Is Nothing Then
          t = Obj.Name
          col = Cells(i, 2).End(xlToRight).Column
          n = ActiveSheet.ListObjects(t).ListColumns(col).Name
          Cells(i, "B").Formula = "=" & t & "[[#This Row],[" & n & "]]"
      Else
          Cells(i, "B").Formula = "=" & Cells(i, 1).End(xlToRight).Address(0, 0)
        End If
      
      Next
      Application.AutoCorrect.AutoFillFormulasInLists = True
    End Sub

  3. #13
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Loop through data and create formulas

    try

    Code:
    Sub Macro1()
      Dim Obj As Object, t As String, i As Long, col As Long, n As String, ini
      Application.AutoCorrect.AutoFillFormulasInLists = False
      For i = 3 To 5
        Set Obj = Range("B" & i).ListObject
        If Not Obj Is Nothing Then
          t = Obj.Name
          ini = Obj.Range.Cells(1, 1).Column
          col = Cells(i, "B").End(xlToRight).Column - ini + 1
          n = ActiveSheet.ListObjects(t).ListColumns(col).Name
          Cells(i, "B").Formula = "=" & t & "[[#This Row],[" & n & "]]"
        Else
          Cells(i, "B").Formula = "=" & Cells(i, "B").End(xlToRight).Address(0, 0)
        End If
      Next
      Application.AutoCorrect.AutoFillFormulasInLists = True
    End Sub
    Regards Dante Amor

  4. #14
    New Member
    Join Date
    May 2019
    Posts
    46
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Loop through data and create formulas

    Great, Dante. Works perfectly!

  5. #15
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,208
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Loop through data and create formulas

    I'm glad to help you. Thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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