Write Flexible Formula

  • Hi,
    I have a sum field in excel which contains the formula to compute sum of certain cells. For eg. Cell C1 has value (C2+C4).


    I have written a vba code to find cells whose value are to be included in the sum field (variables Lrow1,Lrow2 and Lcolumn1,Lcolumn2 contains the row and column number respectively). These cells may differ each time vba code runs. Can I write formula in the sum field based on Lrow and Lcolumn variables.


    Regards
    Tgit

  • Re: Write Flexible Formula


    Quote from tgit

    Can I write [a] formula in the sum field based on Lrow and Lcolumn variables[?]


    Yes, you can; and you'll probably do a fine job at it too. : D


    I really don't have a clue what you want to know.

  • Re: Write Flexible Formula


    I use following line of code to write formula in a cell:
    ActiveSheet.Cells(1, 3).Formula = "=sum(C2,C4)".
    Can I write the 'C2 and C4' in the above formula dynamically?

  • Re: Write Flexible Formula


    Quote from tgit


    Can I write the 'C2 and C4' in the above formula dynamically?


    OK. Say that I want the Sum formula to appear in cell C1. I'm looking at 1-row directly below it for the first number (cell C2), and 3-rows below it for the second number (cell C4). But the column is the same, so that doesn't need to change. Therefore, I'd write:


    Code
    ActiveCell.FormulaR1C1 = "=R[1]C+R[3]C"


    If I were looking at the rows above to be added, I'd use a negative-number.


    Now, if I shift the ActiveCell to cell D1 and run the code, it will then give me the value of D2+D4. Is that what you're asking?

  • Re: Write Flexible Formula


    In the line:
    ActiveSheet.Cells(1, 3).Formula = "=sum(C2,C4)"
    the row may not be 2 and 4. The exact row number will be in variables Lrow1 and Lrow2. And I need to write the formula based on these variables.

  • Re: Write Flexible Formula


    Code
    Sub VariableFormula()
    Dim a As Long, b As Long, c As Long, d As Long
    ' syntax for cells is row, column
    a = 2
    c = 4
    b = 3
    d = 3
    ' same as C2 + C4
        ActiveCell.FormulaR1C1 = Cells(a, b) + Cells(c, d)
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!