Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Loop through data and create formulas

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

    Default Loop through data and create formulas

    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!

    A B C D
    1 1
    2 2
    3 3

    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 by jwburritt; Sep 19th, 2019 at 03:50 PM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,794
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Loop through data and create formulas

    Are you really just trying to come up with a formula in column A to return the first value to the right?
    If so, just use this formula in cell A2 and copy down.
    Code:
    =MATCH(TRUE,INDEX(B2:D2<>0,),0)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    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 so much. Hadn't thought of that! But it's part of a much larger sheet. And depending on in which column the data sits, the formula as to change. Here's an example. The data in col B needs to be negative and put in col a in a formula. The rest are simple formulas.

    A B C D
    -1 1
    2 2
    3 3
    Last edited by jwburritt; Sep 19th, 2019 at 04:10 PM.

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

    Default Re: Loop through data and create formulas

    Returning to your original macro. Try this

    Code:
    Sub temp()
      Dim i As Long
      For i = 2 To 4
        Cells(i, "A").Formula = "=" & Cells(i, 1).End(xlToRight).Address
      Next i
    End Sub
    Regards Dante Amor

  5. #5
    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, Dante. Works fine. But, it produces absolute cell references. I need relative because it's going to get thrown into a table with existing data. Possible? I've tried everything and can't make it work. Ugh.

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

    Default Re: Loop through data and create formulas

    Quote Originally Posted by jwburritt View Post
    Thanks, Dante. Works fine. But, it produces absolute cell references. I need relative because it's going to get thrown into a table with existing data. Possible? I've tried everything and can't make it work. Ugh.
    Try:

    Code:
    Sub temp()
      Dim i As Long
      For i = 2 To 4
        Cells(i, "A").Formula = "=" & Cells(i, 1).End(xlToRight).Address(0, 0)
      Next i
    End Sub
    Regards Dante Amor

  7. #7
    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 Dante!

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 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

  9. #9
    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

    Hello again: I just discovered that the address of the cell reference above needs to also return a structured table address so when I dump the data into the table, the formulas match. In the target table, the formula address are in this form:

    =[@[Header]]

    They are replicated down in the format above because of the excel calculated column feature in tables. I'm willing to workaround by changing all the formulas in the target table to plain relative formula references -- which Dante's solution provides -- but just thought I'd see if there's vba code that can give a structured table address.

    Thanks again!

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

    Default Re: Loop through data and create formulas

    Quote Originally Posted by jwburritt View Post
    Hello again: I just discovered that the address of the cell reference above needs to also return a structured table address so when I dump the data into the table, the formulas match. In the target table, the formula address are in this form:

    =[@[Header]]

    They are replicated down in the format above because of the excel calculated column feature in tables. I'm willing to workaround by changing all the formulas in the target table to plain relative formula references -- which Dante's solution provides -- but just thought I'd see if there's vba code that can give a structured table address.

    Thanks again!

    I found this way, maybe it will help you.

    Code:
    Sub Macro1()
      Dim Obj As Object, t As String, i As Long, col As Long, n As String
      Application.AutoCorrect.AutoFillFormulasInLists = False
      For i = 2 To 4
        Set Obj = Range("A" & i).ListObject
        If Not Obj Is Nothing Then
          t = Obj.Name
          col = Cells(i, 1).End(xlToRight).Column
          n = ActiveSheet.ListObjects(t).ListColumns(col).Name
          Cells(i, "A").Formula = "=" & t & "[[#This Row],[" & n & "]]"
        Else
          Cells(i, "A").Formula = "=" & Cells(i, 1).End(xlToRight).Address(0, 0)
        End If
      Next
      Application.AutoCorrect.AutoFillFormulasInLists = True
    End Sub
    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
  •