Trouble finding row of element zero of ParamArray

Question

I have an array where in column A different article names are listed. Now I want to add up the values corresponding to some of these article names. Since the number of article names is not constant I am passing the names on as a ParamArray.

All of this works well for elements one to the end of the ParamArray, but an error message "Object variable or with block variable not set" appears when I try to find the row of the article number placed in ParamArray(0). Nevertheless, accessing ParamArray(0) is not a problem, but Vba refuses to find the corresponding row.

Here is the code calling the function (col_ML is the column of the values that are added up):

.Cells(63, col_year).Value = Compute_sum_ML(col_ML, "17.8.32.000", "17.8.42.000")

Here is the function itself:

Function Compute_sum_ML(col_ML As Integer, ParamArray article() As Variant) As Double

  Dim row_article As Integer
  Dim result As Double

  row_article = 0
  result = 0

  For i = 0 To UBound(article, 1)
    row_article = d_ML.Range("A:A").Find(What:=article(i),LookIn:=xlValues).row
    If row_article <> 0 Then
      result = result + d_ML.Cells(row_article, col_ML).Value
    End If
  Next i

  Compute_sum_ML = result
End Function

Also I tried defining the ParamArray as string since only strings will be passed on to it but it forces me to define it as variant.

Answer 1

The following works for me. I have replaced your sheet code name reference with Worksheets("Sheet1").

Note I have added a test for if a match found.

Option Explicit
Public Sub test()
    Debug.Print Compute_sum_ML(2, "17.8.32.000", "17.8.42.000")
End Sub

Public Function Compute_sum_ML(ByVal col_ML As Integer, ParamArray article() As Variant) As Double
    Dim row_article As Long, i As Long, result As Double, found As Range

    row_article = 0
    result = 0

    For i = 0 To UBound(article, 1)
        Set found = Worksheets("Sheet1").Range("A:A").Find(What:=article(i), LookIn:=xlValues)
        If Not found Is Nothing Then
            row_article = found.Row
            result = result + Worksheets("Sheet1").Cells(row_article, col_ML).Value
         'Exit For  ''<==If only one match wanted.
        End If
        Set found = Nothing
    Next i
    Compute_sum_ML = result
End Function

---

Data: