View original (French)

Macro, sum on a variable column

Bob -  
 Bob -
Hello,

After extensive research, I cannot adapt the different codes I've found to my issue,

adding a variable sum based on the number of rows?
summing cells from B2 to the last line of column B where the result is located at the end of the data range,

http://www.cjoint.com/c/FJkqTsvuV6g

Could you please tell me which code to use?
Thank you.

4 answers

  1.
    ccm81 Posted messages 11033 Status Member 2 434
     
    Hello

    Are you sure you need a macro?
    Formula in B16
    =SUM(OFFSET($A$2,0,1,COUNTA(A:A)-2,1))
    If you insert a line before the total line, it will be taken into account (as long as column A is filled)

    Best regards
    1
  2. ccm81 Posted messages 11033 Status Member 2 434
     
    Here's the translation: Go for a macro 
    Dim lifin As Long, plage As Range
    With Sheets("Sheet1")
     lifin = Range("A" & Rows.Count).End(xlUp).Row
     Set plage = Range("B2:B" & lifin)
     Range("A" & lifin + 1).Value = "total"
     Range("B" & lifin + 1).Value = Application.WorksheetFunction.Sum(plage)
    End With

    Cdmltn
    1
  3. Bob
     
    Hello, thank you for your reply.

    Yes, indeed, the sheet is deleted at each update.
    Column A is updated by a macro from a detailed file, this formula that I wish to automate with the rest of the macro is meant to synthesize information.

    Thank you in advance.
    0