CODEWITHSUNDEEP
vbaexcel
user882670
user882670

Reputation:

Custom function to sum values in row with specific text

I want to sum a variable sized row with values: From the 5th column to the last filled cell in row 6.

The row in question must contain "My text" in column B.

So, for example, I want to sum the values below from E8 to J8: enter image description here

This is the code I'm using:

Function cenas()
Application.Volatile

Dim celula
Dim ultAno As Integer
Dim intervalo  As Range
Dim separador As Worksheet

    Set separador = Sheets("Sheet1")
    ultAno = separador.Cells(6, 5).End(xlToRight).Column
    For i = 1 To 50
      celula = separador.Cells(i, 2).Value
      If celula = "My text" Then
        Set intervalo = Sheets(separador).Range(Sheets(separador).Cells(i, 5), Sheets(separador).Cells(i, ultAno))
        cenas = Application.Sum(intervalo)
      End If
    Next i

End Function

Which is returning #VALUE!

I tried different approaches related with the variables declarations, but can't find a solution.

Upvotes: 2

Views: 803

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

One problem is with this line

Set intervalo = Sheets(separador).Range(Sheets(separador).Cells(i, 5), Sheets(separador).Cells(i, ultAno))

There is no need to use the Sheets(). separador is set as a sheet.

Function cenas()
Application.Volatile

Dim celula
Dim ultAno As Integer
Dim intervalo  As Range
Dim separador As Worksheet

    Set separador = Sheets("Sheet1")
    ultAno = separador.Cells(6, 5).End(xlToRight).Column
    For i = 1 To 50
      celula = separador.Cells(i, 2).value
      If celula = "My text" Then
        Set intervalo = separador.Range(separador.Cells(i, 4), separador.Cells(i, ultAno))
        cenas = Application.sum(intervalo)
      End If
    Next i

End Function

If you are going to use a function, why not make it a little more versatile:

Function cenas(lkpStr As String)
Application.Volatile

Dim celula
Dim ultAno As Integer
Dim intervalo  As Range
Dim separador As Worksheet

    Set separador = Sheets("Sheet3")
    ultAno = separador.Cells(6, 5).End(xlToRight).Column
    For i = 7 To separador.Cell(7,2).End(xlDown).Row

      If separador.Cells(i, 2).value = lkpStr Then
        Set intervalo = separador.Range(separador.Cells(i, 5), separador.Cells(i, ultAno))
        cenas = Application.sum(intervalo)

      End If
    Next i

End Function

Sub getsum()
Dim t
t = cenas("My Text")
Debug.Print t

End Sub

This will allow you to specify the text for which you want to search. And return the sum of the last one in the group. You can call this as a UDF in your sheet or by vba code.

Note: if there is more than one "My text" in column B it will only return the last row with "My text" in it.

Upvotes: 2

MatthewD
MatthewD

Reputation: 6761

Look at something like this.

Private Sub CommandButton34_Click()
    Dim ws1 As Excel.Worksheet
    Dim lRow As Long
    Dim lLastCol As Long

    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    lRow = 1

    ws1.Activate
    'Loop through the rows
    Do While lRow <= ws1.UsedRange.Rows.count

        If ws1.Range("B" & lRow).Value = "My text" Then

            'Get the last col used in that row
            lLastCol = ws1.Cells(lRow, ws1.Columns.count).End(xlToLeft).Column

            'Write the sum to the column after the last used column
            ws1.Cells(lRow, lLastCol + 1).Value = "=sum(D" & lRow & ":" & Col_Letter(lLastCol) & lRow & ")"

        End If
        lRow = lRow + 1
    Loop

End Sub

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Upvotes: 1

Related Questions