How to sum with arguments in vba?

Question

I have the following code:

Option Explicit


Function SumAboveV(column As Range)

Worksheets("Sheet16").Activate
Dim r As Range, rAbove As Range
Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
Set r = zelle.Offset(0, -2)
Set rAbove = Range(r.Offset(-1, 0), Cells(2, r.column))
column.Value = wf.Sum(rAbove)

End Function

All I want to have is to sum up all numbers I have in row B until the cell where I place the function so e.g. as you see in the attached screenshot I want to sum up all values from B2 until that cell and place the total value in row D.

I don't understand my mistake. Any ideas?

QHarr · Accepted Answer · 2018-05-21 16:58:46Z

1

Like

Public Function SumUntil(ByRef rng As Range) As Double
    SumUntil = Application.WorksheetFunction.Sum(Range(Cells(2, rng.Column), rng))
End Function

Usage:

You add an optional start row for summing with

Public Function SumUntil(ByRef rng As Range, Optional ByVal startRow As Long = 3) As Double
    SumUntil = Application.WorksheetFunction.Sum(Range(Cells(startRow, rng.Column), rng))
End Function

edited May 21, 2018 at 16:58

answered May 21, 2018 at 16:35

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lux Claridge · Accepted Answer · 2018-05-21 16:31:17Z

0

I might be missing something, but why not have just use sum in column D? if you use "=Sum($B2:B3)" Then drag that formula down to wherever. Then every cell in D would have a sum up until that row. It may be simpler than writing a whole macro for it.

answered May 21, 2018 at 16:31

Lux Claridge

2055 silver badges18 bronze badges

Comments

Darren Bartrup-Cook · Accepted Answer · 2018-05-21 16:33:17Z

0

You could use the formula:

=SUM($B$1:INDEX($B:$B,ROW()-1))

Placed in cell B367 it returns the sum of B1:B366 and doesn't give a circular reference warning.

answered May 21, 2018 at 16:33

Darren Bartrup-Cook

20.2k2 gold badges27 silver badges50 bronze badges

Comments

JohnyL · Accepted Answer · 2018-05-21 20:09:34Z

0

Instead of creating Range variables and all those manipulations, you can rely on R1C1 notation. Using R1C1 makes reference dynamic - no matter where you insert this formula in column D, it will get correct address. You just won't have to do extra manipulations.

zelle.FormulaR1C1 = "=SUM(RC[-2]:R2C[-2])"

edited May 21, 2018 at 20:09

answered May 21, 2018 at 16:28

JohnyL

7,1845 gold badges27 silver badges48 bronze badges

3 Comments

Joe McMahon Over a year ago

More detail as to why this is the right answer would be preferable.

JohnyL Over a year ago

@JoeMcMahon Being inserted into column D, it doesn't need to calculate offsets, creating Range variables. It's just dynamic.

Joe McMahon Over a year ago

Could you add that to the answer? That would be helpful for others in the future to better understand how it works.

DisplayName · Accepted Answer · 2018-05-21 21:18:21Z

0

All I want to have is to sum up all numbers I have in row B until the cell where I place the function

then go this way:

Public Function SumAboveV() As Double
    SumAboveV = Application.WorksheetFunction.Sum(Range("B2", Cells(Application.Caller.Row, "B")))
End Function

and you don't need to pass any parameter to your function, since Application.Caller.Row will get the row index of the "calling" cell

if you want it to update at any sheet recalculation then add Application.Volatile statement

Public Function SumAboveV() As Double
    Application.Volatile
    SumAboveV = Application.WorksheetFunction.Sum(Range("B2", Cells(Application.Caller.Row, "B")))
End Function

answered May 21, 2018 at 21:18

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Collectives™ on Stack Overflow

How to sum with arguments in vba?

5 Answers 5

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related