title: Custom function to sum values in row with specific text
body: <p>I want to sum a variable sized row with values: From the 5th column to the last filled cell in row 6.</p>
  
  <p>The row in question must contain "<code>My text</code>" in column <code>B</code>.</p>
  
  <p>So, for example, I want to sum the values below from <code>E8 to J8</code>:
  <a href="https://i.stack.imgur.com/aI8ap.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aI8ap.jpg" alt="enter image description here"></a></p>
  
  <p>This is the code I'm using:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Which is returning <code>#VALUE!</code></p>
  
  <p>I tried different approaches related with the variables declarations, but can't find a solution.</p>