CODEWITHSUNDEEP
vbaexcel
Brook Julias
Brook Julias

Reputation: 2105

VBA, Excel Error 'object required' when passing an array as a variable

I have two VBA procedures one building an array which it will then pass to another. However, when the first procedure is run an Excel Error window pops up saying 'object required'. Here is the code I have. I cannot seem to find what is causing this error.

Sub DetermineLoad()

Worksheets("Gear").Activate

Dim row_src As String
Dim strt_row As String
Dim col_storage As String
Dim Arr() As String
Dim aLength As Integer

row_src = "C"
strt_row = "9"
col_storage = "B"

Range(row_src + strt_row).Activate

While Not IsEmpty(Range(col_storage + Active.Row))
    If Not IsEmpty(ActiveCell) Then
        If ActiveCell.Value Then
            aLength = UBound(Arr) + 1
            ReDim Arr(0 To aLength)
            Arr(aLength) = String(ActiveCell.Column, ActiveCell.Row)
        End If
    End If
    ActiveCell.Offset(0, 1).Activate
Wend

DetermineWeight Arr

End Sub
Sub DetermineWeight(ParamArray Arr() As Variant)

Worksheets("Gear").Activate

Dim weight_lb_trgtCell As String
Dim weight_oz_trgtCell As String
Dim volume_cuin_trgtCell As String
Dim volume_liter_trgtCell As String
Dim col_oz As String
Dim col_lb As String
Dim weight_lb As Double
Dim weight_oz As Double
Dim oz_to_pound As Integer
Dim row_src As String
Dim src_range As Range

weight_lb_trgtCell = "H4"
weight_oz_trgtCell = "I4"

col_oz = "H"
col_lb = "I"
weight_lb = 0       ' 0 out `weight_lb`
weight_oz = 0       ' 0 out `weight_oz`
oz_to_pound = 16    ' 16 ounces to 1 pound

'get sum of weights (pounds, ounces)
For n = LBound(Arr) To UBound(Arr)

    src_range = Range(Arr(n))

    src_row = src_range.Row
    weight_oz = weight_oz + Range(col_oz + src_row).Value
    weight_lb = weight_lb + Range(col_lb + src_row).Value

Next n

'convert pounds
weight_lb = weight_lb + Int(weight_oz / oz_to_pound)
weight_oz = weight_oz - ((weight_oz / oz_to_pound) * oz_to_pound)

Range(weight_lb_trgtCell) = weight_lb
Range(weight_oz_trgtCell) = weight_oz

End Sub

Upvotes: 0

Views: 1652

Answers (2)

BiggerDon
BiggerDon

Reputation: 31

An alternative to passing the array is to scope the array at the Module then use a Dynamic Named Range (as suggested), then the redim would be the rows of the Dynamic Named Range

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

Why do you need VBA for this? Put this formula in cell H4 (leftover ounces):

=MOD(SUMIF(C9:C1000,"<>",H9:H1000),16)

And this formula in cell I4 (total pounds):

=SUMIF(C9:C1000,"<>",I9:I1000)+INT(SUMIF(C9:C1000,"<>",H9:H1000)/16)

Adjust ranges to suit. If you need it to pick up new rows as they come in or are removed, use a dynamic named range.

Upvotes: 1

Related Questions