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.

Is it actually finding something in the sheet? It works for me. It will produce that error if there is no match found. You risk type mismatch however with d_ML.Cells(row_article, col_ML).Value, and you should add handling for if not found. — QHarr, Jul 06 '18 at 07:59
Regarding your last remark; Because of each argument of an ParamArray can be a different DataType, you can only declare it as an Variant. — EvR, Jul 06 '18 at 08:02

QHarr · Answer 1 · 2018-07-06T09:05:30.263

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:

Trouble finding row of element zero of ParamArray

1 Answers1