15

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 it doesn't work.

Brian Webster
  • 30,033
  • 48
  • 152
  • 225
haver24
  • 241
  • 2
  • 5
  • 11
  • "it doesn't work" is most likely not specific enough to help you. What do you expect to happen? What happens instead? Do you get an error message? If so, what error message? – Codo Jul 29 '12 at 09:36

4 Answers4

43

Function is not a property/method from range.

If you want to sum values then use the following:

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

EDIT:

if you want the formula then use 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).

In case you are allways going to use the same range address then you can use as Rory sugested:

Range("A1").Formula ="=Sum(A2:B3)"
CaBieberach
  • 1,748
  • 2
  • 17
  • 26
  • Thanks Cabieberach ! It works, but I need sth else. Codo is right. I should have written what I expect. I would like to have formula (not just a result) in Range("A1") that sums two cells. I mean, if I change value in Cells(2,1) the value in Range("A1") should change also. Thanks in advance and sorry for my bad English. – haver24 Jul 29 '12 at 13:52
  • 2
    Try `Range("A1").Formula = "=Sum(A2,B3)"` You have to use the name of the cell location (A2) rather than use the Cells(2,1) vba function. – Rory Jul 30 '12 at 04:53
  • 1
    The function that works best for me is Excel.WorksheetFunction.Sum() – Ross Brasseaux Jul 16 '13 at 21:22
10

Place the function value into the cell

Application.Sum often does not work well in my experience (or at least the VBA developer environment does not like it for whatever 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 into the cell

The other method which you were looking for I think is to place the function directly into the cell. This can be done by inputting the function string into the cell value. Here is an example that provides the same result as above, except the cell value is given the function and not the result of the function:

    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.
Ross Brasseaux
  • 3,879
  • 1
  • 28
  • 48
  • Regarding the second point: If you are wondering why your formula is in the right cell, but does not change from zero and does not throw any errors, you might have accidentally included your formula cell within the input cells (off-by-one errors are hard...) – user121391 Nov 18 '16 at 18:01
3
Range("A1").Function="=SUM(Range(Cells(2,1),Cells(3,2)))"

won't work because worksheet functions (when actually used on a worksheet) don't understand Range or Cell

Try

Range("A1").Formula="=SUM(" & Range(Cells(2,1),Cells(3,2)).Address(False,False) & ")"
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Thanks man ! I deleted False, False in Address() and it works ! Now I can finish my application. Greetings from Warsaw, Bartek – haver24 Jul 30 '12 at 11:45
  • Hi Tim, Are you using `.function` or did you meant `.WorksheetFunction` ? – CaBieberach Aug 01 '12 at 12:39
  • Ha - should be .Formula. That comes from just copying the OP without looking. Will correct. Thanks. – Tim Williams Aug 01 '12 at 14:56
  • @TimWilliams Thank you , you are my hero!! I have spent all afternoon for a generic solution so I can use inside a for loop and your answer helped me to get to it 'rowRange.Cells(LastRow+1, j) = "=SUM(" & Range(Cells(i, j), Cells(LastRow, j)).Address(False, False) & ")"' – katta Sep 09 '16 at 03:55
1
Range("A10") = WorksheetFunction.Sum(Worksheets("Sheet1").Range("A1", "A9"))

Where

Range("A10") is the answer cell

Range("A1", "A9") is the range to calculate

Ram
  • 3,092
  • 10
  • 40
  • 56