Help with Dynamic Array

[Elogger]

[TABLE="width: 512"]
<tbody>[TR]
[TD="colspan: 8"]Can someone please help me with this? I'm really struggling. Write a macro reports the sum of squares of negative #'s, sum of squares of the positive #'s, the maximum and the minimum numbers in a messagebox. You do not know the size of the table but can assume the user will choose the first cell (upper left cell) of the table and run your macro. [/TD]
[/TR]
</tbody>[/TABLE]

 

[pbornemeier]

Sub DoHomeworkProblem()

    Dim rngCell As Range
    Dim rng As Range
    Dim lMax As Long
    Dim lMin As Long
    Dim lPosSQ As Long
    Dim lNegSQ As Long

    Set rng = Selection.CurrentRegion
    
    For Each rngCell In rng
        Select Case rngCell.Value
        Case Is > 0
            lPosSQ = lPosSQ + rngCell.Value ^ 2
        Case Else
            lNegSQ = lNegSQ + rngCell.Value ^ 2
        End Select
        If rngCell.Value > lMax Then lMax = rngCell.Value
        If rngCell.Value < lMin Then lMin = rngCell.Value
    Next
    
    MsgBox "Number of Cells: " & rng.Cells.Count & vbLf & _
        "Sum of Positive #s Squared: " & lPosSQ & vbLf & _
        "Sum of Negative #s Squared: " & lNegSQ & vbLf & _
        "Max Value: " & lMax & vbLf & _
        "Min Value: " & lMin, , "Homework Answers"
        
End Sub