Use Variables to Write Formula

  • Hi all,


    Here is my code, which does not generate the desired result:

    Code
    1. Dim c, rng As Range, i, j As Long, Txt1, Txt2 As String
    2. i = Range("A" & Rows.Count).End(xlUp).Row
    3. Txt1 = "E"
    4. Txt2 = "G"
    5. Set rng = Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
    6. For j = 2 To i
    7. For Each c In rng
    8. Cells(j, 9).Formula = "=" & Txt1 & j & Txt2 & j
    9. Next c
    10. Next j


    The result, in J2 for example, is:


    ='E2'+'G2'


    So, while the result is very close, I need to have the formula without the ' characters.


    Any help would be appreciated!

  • Not sure why you are using variables like that, nor why adding formulas individually to each row rather than using AutoFill or FillDown.


    But try this with your current code


    Code
    1. Cells(j, 9).Formula = "=Sum(" & Txt1 & j & "," & Txt2 & j & ")"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • This should be all you need

    Code
    1. Sub test()
    2. With Cells(2, 9)
    3. .Formula = "=Sum(E2,G2)"
    4. .Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1).FillDown
    5. End With
    6. End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • or you could use

    Code
    1. .Formula = "=E2+G2"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Excellent, KjBox! That worked well, thank you!


    I know it seems wrong to hard-code cell references, but after my code massages the data into a set format, I need to add additional formulae to the extracted data.