About the sum function

[Hernan_g_f]

Hello all,

I have a matrix in Excel in a worksheet "1" in a workbook "Workboo1.xlsm"

Column 1 Column 2 Column 3 Column 7

Row 1 1 4 4 sum (A1: C1)
Row 2 0 4 5 sum (A2: C2)
Row 3 8 10 11 sum (A3:C3)

Row 5 sum sum sum
(A1:A3) (B1:B3) (C1:C3)

How can I wrote in VBA language the sum function for the rows and colunms?
And if I want to copy the column 7 in column 10 as value?

Many thanks for your help!
Hernán

 

[Joe4]

You can use the SUM function in VBA by prefacing it with "Application.WorksheetFunction.Sum", i.e.

Sub MyTest()
    Dim rng As Range
    Set rng = Range("A1:C1")
    MsgBox Application.WorksheetFunction.Sum(rng)
End Sub

 

[Joe4]

Row 5 sum sum sum
(A1:A3) (B1:B3) (C1:C3)

So, if you want to populate row 5 with the sums of each of those, you could also use this (to input the SUM formulas in row 5):

    Range("A5:C5").FormulaR1C1 = "=SUM(R[-4]C:R[-2]C)"

(you can get this code by simply turning on your Macro Recorder and entering the sum formulas in row 5).

If you wanted to change those to hard-coded values, you can then follow the line above with:

    Range("A5:C5").Value = Range("A5:C5").Value