CODEWITHSUNDEEP
excelvbauser-defined-functions
Sheillah Kisaame
Sheillah Kisaame

Reputation: 21

VBA: UDF summing a range of cells until nth column

Im a super newbie to VBA programming and I am trying to make a user defined function where I sum up a range of cells until the nth column based on LT. I know I can use the offset formula but I want to learn how to either use a for loop with this or something else.

Below is what I am trying to achieve and what I have in code. Please give your guidance.

enter image description here

Function addtill(LT As Variant, rangeA As Variant) As Variant
k = LT
  For i = 2 To 10
  For j = 3 To 12
addtill = rangeA(i, j) + rangeA(i, LT)
  Next j
  Next i
End Function

Upvotes: 1

Views: 193

Answers (2)

user3598756
user3598756

Reputation: 29421

You may be after what follows:

Public Function AddTill(areaToSum As Range, LTRange As Range) As Double
    
    Dim sum As Double
    
    Dim cel As Range
        For Each cel In areaToSum .Columns(1).Cells
            sum = sum + WorksheetFunction.sum(cel.Resize(, Intersect(LTRange, cel.EntireRow)))
        Next
    
    AddTill = sum
End Function

the function accepts two arguments:

  • areaToSum

    the range whose cells are to be summed up

  • LTRange

    the range where to grab corresponding "LT" values

It loops through the areaToSum first column cells and keeps updating the sum with the sum of the range obtained resizing the initial cell to the number of columns read from the corresponding cel of LTRange

While should you need the sum row by row, you could use this function:

Public Function AddTill2(areaToSum As Range, LTRange As Range) As Double

    Dim cel As Range
        Set cel = Application.Caller ' get the cel the function is being called from
        AddTill2 = WorksheetFunction.sum(Intersect(areaToSum, cel.EntireRow).Resize(, Intersect(LTRange, cel.EntireRow)))

End Function

Upvotes: 1

PeterT
PeterT

Reputation: 8557

If I'm understanding your requirements for a UDF, you want to sum up the number of columns given by LT in a given range of cells. The example below may help.

Option Explicit

Public Function AddTill(ByVal LT As Long, ByRef area As Range) As Double
    '--- returns a sum of values from the area, starting in the
    '    first column and "adding until" the number of columns
    '    given in LT 
    '    NOTE: this function will add columns from multiple rows if
    '          the area Range has more than one row
    
    '--- if LT is greater than the number of columns in the area,
    '    then only sum the values in the area (LT would push us
    '    past the area's columns)
    Dim sumColumns As Long
    sumColumns = IIf(LT <= area.Columns.Count, LT, area.Columns.Count)
    
    Dim result As Double
    Dim i As Long
    Dim j As Long
    For i = 1 To area.Rows.Count
        For j = 1 To sumColumns
            result = result + area.Cells(i, j).Value
        Next j
    Next i
    AddTill = result
End Function

A few important points to note:

  1. ALWAYS use Option Explicit in your code modules.
  2. Try to be clear about what the function is doing and how it's doing it. Using appropriate variable names helps quite a bit in "self-documenting" your code.
  3. You used Variant as the types for your parameters and result. This leaves you wide open to send ANY type of value to your function (including a String or a Boolean), which is not really what you want. It's a better practice to explicitly type your parameters and all variables for what they really are. Otherwise, you will cross up different kinds of values and it's difficult to find the problem.

Upvotes: 0

Related Questions