Loop through data and create formulas

[jwburritt]

Hello: I have the following data. I want col A to equal the value of the first cell to its right, but retain it in a formula. For example the successful result in A2 would be "=B2" not "1". The following code gets the value in col A but it's a value, not a formula. Thank you!

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"]3[/TD]
[/TR]
</tbody>[/TABLE]

Sub temp()

    Dim i As Long
    For i = 2 To 4
    
        Cells(i, 1).Formula = Cells(i, 1).End(xlToRight).Value


    Next i


End Sub

 

[jwburritt]

Thank you! I'll check it out asap.

 

[jwburritt]

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.

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

 

[DanteAmor]

try

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

 

[jwburritt]

Great, Dante. Works perfectly!

 

[DanteAmor]

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