Announcement

Collapse
No announcement yet.

Use Variables to Write Formula

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use Variables to Write Formula



    Hi all,

    Here is my code, which does not generate the desired result:
    Code:
        Dim c, rng As Range, i, j As Long, Txt1, Txt2 As String
        i = Range("A" & Rows.Count).End(xlUp).Row
        Txt1 = "E"
        Txt2 = "G"
        Set rng = Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
        For j = 2 To i
            For Each c In rng
                Cells(j, 9).Formula = "=" & Txt1 & j & Txt2 & j
            Next c
        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!

  • #2
    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:
    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.

    Comment


    • #3
      Thanks, KjBox,

      I'll look up the FiilDown, too, as this may be better.

      Comment


      • #4
        This should be all you need
        Code:
        Sub test()
            With Cells(2, 9)
                .Formula = "=Sum(E2,G2)"
                .Resize(Cells(Rows.Count, 1).End(xlUp).Row - 1).FillDown
            End With
        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.

        Comment


        • #5
          or you could use
          Code:
          .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.

          Comment


          • #6
            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.

            Comment


            • #7


              You're welcome
              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.

              Comment

              Working...
              X