locked
Using Sum function in VBA RRS feed

  • Question

  • I am trying to use a variable instead of number -4 in the following formula:

    SUM(R[-4]C:R[-1]C)

    Please help me do this

        KpAdng = -4
        ActiveCell.FormulaR1C1 = "=SUM(R[KpAdng]C:R[-1]C)"
        For n = 1 To 5
         KpAdng = KpAdng - 1
         ActiveCell.FormulaR1C1 = "=SUM(R[KpAdng]C:R[-1]C)"
        Next n 

    I am getting an error.  Can this be done this way or do I need to change the thought process.  I am trying to help my son understand basic level of coding and he asked me if he can do the above.  I have no clue, I thought I could handle basic stuff but I have forgotten a lot.  Please help if you can.  Thanks :)

    • Edited by JPartss Friday, November 13, 2020 12:36 AM
    Friday, November 13, 2020 12:27 AM

All replies

  • The variable should be outside the quotes. Use

        KpAdng = -4
        ActiveCell.FormulaR1C1 = "=SUM(R[" & KpAdng & "]C:R[-1]C)"
        For n = 1 To 5
         KpAdng = KpAdng - 1
         ActiveCell.FormulaR1C1 = "=SUM(R[" & KpAdng & "]C:R[-1]C)"
        Next n

    Remark: you don't change ActiveCell in this code, so you're writing a formula to the same cell 6 times.


    Regards, Hans Vogelaar (https://www.eileenslounge.com)

    Friday, November 13, 2020 8:39 AM