Loop through data and create formulas

jwburritt

New Member
Joined
May 22, 2019
Messages
49
Office Version
  1. 365
Platform
  1. Windows
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!

ABCD
11
22
33

<tbody>
</tbody>

Code:
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
 
Last edited:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top