Geek asks and answers

Sum function in VBA

I have a problem with summing cells in vba. I need to use cells (a, b):

Range("A1").function="=SUM(Range(Cells(2,1),Cells(3,2)))"

but that will not work.

+7
source share
5 answers

A function is not a property / method out of range.

If you want to summarize the values, use the following:

Range("A1").Value = Application.Sum(Range(Cells(2, 1), Cells(3, 2)))

EDIT:

if you want the formula to be used as follows:

Range("A1").Formula = "=SUM(" & Range(Cells(2, 1), Cells(3, 2)).Address(False, False) & ")"

'The two false after Adress is to define the address as relative (A2:B3).
'If you omit the parenthesis clause or write True instead, you can set the address
'as absolute ($A$2:$B$3).

If you always use the same range address, you can use it as a Rory:

Range("A1").Formula ="=Sum(A2:B3)"
+29
source

Put the function value in the cell

Application.Sum often does not work well in my experience (or at least the VBA developer environment is not like for any reason).

The function that works best for me is Excel.WorksheetFunction.Sum()

Example:

Dim Report As Worksheet 'Set up your new worksheet variable.
Set Report = Excel.ActiveSheet 'Assign the active sheet to the variable.

Report.Cells(11, 1).Value = Excel.WorksheetFunction.Sum(Report.Range("A1:A10")) 'Add the function result.

Place the function directly in the cell

, , , . , . , , , , , :

    Dim Report As Worksheet 'Set up your new worksheet variable.
    Set Report = Excel.ActiveSheet 'Assign the active sheet to the variable.

    Report.Cells(11, 1).Value = "=Sum(A1:A10)" 'Add the function.
+8
Range("A1").Function="=SUM(Range(Cells(2,1),Cells(3,2)))"

, ( ) Range Cell

Try

Range("A1").Formula="=SUM(" & Range(Cells(2,1),Cells(3,2)).Address(False,False) & ")"
+3
Range("A10") = WorksheetFunction.Sum(Worksheets("Sheet1").Range("A1", "A9"))

Range("A10") -

Range("A1", "A9") -

+1

Dim As Integer Dim LASTRW As Long

LASTRW = ("GROW PROG"). (Rows.Count, "M"). End (xlUp).ROW

= 4 LASTRW

"ERROR CHECK: FOR IF ALL DAYS THE PHASES EXCEED THE TOTAL TIME PERIOD Dim grper As Integer Dim phper As Integer grper = Sheets (" GROW PROG "). Cells (I," J "). Value phper = Excel.WorksheetFunction **. Sum ** (sheets ("DATA"). Range (cells (I-1, "O"), cells (I-1, "Q")). Value * 7

this is the beginning of my sub. Am I getting an error that ".sum" is an invalid qualifier? any suggestions?

0
source

More articles:


All Articles