Sum with dynamic cells

  • HI,

    Can someone help and will tell me what's wrong with this command:

    Code
    1. Range("r1") = "=sum(" & Range(Cells(1, 1), Cells(Lrow, Lcol)) & ")"

    Thanks

    Nati

  • If you're trying to put a formula in there, you need the address of the range:


    Code
    1. Range("r1") = "=sum(" & Range(Cells(1, 1), Cells(Lrow, Lcol)).Address & ")"


    or just:


    Code
    1. Range("r1").FormulaR1C1 = "=sum(R1C1:R" & Lrow & "C" &  Lcol & ")"


    If you just want the value, then:


    Code
    1. Range("r1").Value = Worksheetfunction.Sum(Range(Cells(1, 1), Cells(Lrow, Lcol)))

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Sorry, I answered to fast.

    The second option worked great

    this one - (Range("r1").FormulaR1C1 = "=sum(R1C1:R" & Lrow & "C" & Lcol & ")")