0

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
6
  • What line is your debugger stopping on? Commented Sep 3, 2013 at 19:28
  • Active.Row Is that valid? Try adding Option Explicit and see if it flags this. Commented Sep 3, 2013 at 19:29
  • The error seems to be thrown from code on Line 17 where the while loop is started. I think ActiveCell.Row works, but I will see about finding a different solution. Commented Sep 3, 2013 at 19:32
  • Active.Row needed to be changed to ActiveCell.Row Commented Sep 3, 2013 at 19:34
  • 1
    Moral of the story is always, unless a trivial throwaway hack for something, use the Option Explicit compiler declaration at the top of the module. The reason it failed was Active is not an existing object (like ActiveCell would be), and it was not explicitly assigned to. Option Explicit would require explicit variable declaration which would result in the compiler warning you about the lack of a variable called "Active". Commented Sep 3, 2013 at 19:55

2 Answers 2

1

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.

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you for this answer. I hate VBA and would rather not have to bother with it if I don't have to.
0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.