CODEWITHSUNDEEP
vbaexcel
jmaz
jmaz

Reputation: 527

Pass Range in Function, Sum Adjacent Cells, and Return Sum

I have the following Excel table:

Sample Image

I want to pass the first column as a string, determine the address of the cells called 'LNA' and 'LCAMP', and sum the adjacent cells 'between' those two addresses. My failed code:

Function LNAtoLCAMP(ComponentList) As Single

Dim i As Integer
Dim LBoundAddress As Variant, UBoundAddress As Variant

For i = LBound(ComponentList) To UBound(ComponentList)
    If ComponentList(i, 1).Value = "LNA" Then
        LBoundAddress = ComponentList(i, 1).Address.Offset(0, 1)
    End If
    If ComponentList(i, 1).Value = "LCAMP" Then
        UBoundAddress = ComponentList(i, 1).Address.Offset(0, 1)
    End If
Next

LNAtoLCAMP = Application.WorksheetFunction.Sum(LBoundAddress, ":", UBoundAddress)

End Function

Maybe there's a better way?

Upvotes: 2

Views: 878

Answers (3)

L42
L42

Reputation: 19727

Try this:

Function LNAtoLCAMP() As Single
   Dim LNA As Range, LCAMP As Range

   With Sheets("Sheet1")
       Set LNA = .Range("B:B").Find("LNA").Offset(0, 1)
       Set LCAMP = .Range("B:B").Find("LCAMP").Offset(0, 1)
       If Not LNA Is Nothing And Not LCAMP Is Nothing Then _
           LNAtoLCAMP = Evaluate("SUM(" & .Range(LNA, LCAMP).Address & ")")
   End With
End Function

Edit2: For your dynamic needs.

Function CONSUM(rng As Range, str1 As String, str2 As String, _
        Optional idx As Long = 1) As Variant
    Application.Volatile '<~~ autoupdate on cell change, remove otherwise
    Dim r1 As Range, r2 As Range
    Set r1 = rng.Find(str1, rng(1), , xlWhole)
    Set r2 = rng.Find(str2, rng(1), , xlWhole, , xlPrevious)
    If Not r1 Is Nothing And Not r2 Is Nothing Then _
        CONSUM = Application.Sum(rng.Parent.Range(r1.Offset(0, idx), _
        r2.Offset(0, idx))) Else CONSUM = CVErr(xlErrValue)
End Function

In the second function, you can select the range you search and also specify the string you want to search.
It returns #VALUE! error if the strings you specify are not found. HTH.

For Edit2 offset is dynamic as well (default at 1). Also this will sum the first instance of the 1st string up to the last instance of the second which was raised by chrisneilsen.

Result:

enter image description here

Upvotes: 4

Chrismas007
Chrismas007

Reputation: 6105

If you insist on using an ApplicationFunction then you need quotes.

Also I believe it should be .offset().address (Needs to be flipped)

TESTED WORKING:

Function LNAtoLCAMP(ByVal ComponentList As Range) As Single
    Dim LBoundAddress As Range, UBoundAddress As Range
    Dim cel As Range
    For Each cel In ComponentList
        If cel.Value = "LNA" Then
            Set LBoundAddress = cel.Offset(0, 1)
        End If
        If cel.Value = "LCAMP" Then
            Set UBoundAddress = cel.Offset(0, 1)
        End If
    Next cel
    LNAtoLCAMP = Application.WorksheetFunction.Sum(Range(LBoundAddress, UBoundAddress))
End Function

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53137

According to your comment you are calling the function as

=LNAtoLCAMP(B16:B61)

This is not passing an array, it is passing a range (that's a good thing)

Your function, modified:

Function LNAtoLCAMP(ComponentList As Range) As Variant
    Dim i As Long
    Dim dat As Variant
    Dim Sum As Double

    Dim LBoundAddress As Long, UBoundAddress As Long

    dat = ComponentList.Value
    For i = LBound(dat, 1) To UBound(dat, 1)
        Select Case dat(i, 1)
            Case "LNA", "LCAMP"
                If LBoundAddress = 0 Then
                    LBoundAddress = i
                End If
                If i > UBoundAddress Then
                    UBoundAddress = i
                End If
        End Select
    Next

    For i = LBoundAddress To UBoundAddress
        Sum = Sum + dat(i, 2)
    Next
    LNAtoLCAMP = Sum
End Function

Call it with both columns in the range

=LNAtoLCAMP(B16:C61)

Note:

  1. I have assumed you want to include hidden rows in the sum, and "Between" includes the rows LNA and LCAMP are on. Both these assumptions are easily modified if required.

  2. I have also assumed you want to sum from the first instance of either string to the last instance of either string. Also easily modified if required.

  3. You could also pass in the search strings to make it more flexable.

  4. You should add error handling, eg if one of the search string is not in the list

Upvotes: 3

Related Questions