Excel VBA: #VALUE! Error with Certain Selected Ranges

Question

Heylo, I have been learning Excel VBA and Macros lately for fun, but I've run into a problem with a practice exercise I'm going through... I have a function that started producing a #VALUE! error with certain selected ranges and I'm slightly confused on how to fix it. The function does different calculations based on what cell is being autofilled. The function is below:

Function CalculateStuff(myRange As Range) As Double
   Application.Volatile

   Dim numRows As Long
   numRows = myRange.Rows.Count

   Dim whatColumn As Long
   whatColumn = Application.Caller.Column - myRange.Column

   Dim i As Long
   Dim thing As Double
   Dim mainThing As Double

   Select Case whatColumn
      Case 0
         CalculateStuff = WorksheetFunction.Sum(myRange.Columns(1))
      Case 1
         CalculateStuff = WorksheetFunction.SumProduct(myRange.Columns(1), myRange.Columns(2))
      Case 2
         CalculateStuff = WorksheetFunction.SumProduct(myRange.Columns(1), myRange.Columns(3)) / WorksheetFunction.Sum(myRange.Columns(1))
      Case 3
         CalculateStuff = WorksheetFunction.SumSq(myRange.Columns(4))
      Case 4
         For i = 1 To numRows
            thing = myRange(i, 1) * WorksheetFunction.SumSq(myRange(i, 3), myRange(i, 5))
            mainThing = mainThing + thing
         Next i
         CalculateStuff = mainThing
    End Select

End Function

And then I use it in a subroutine to populate the active cell's formula and autofill to the right of the active cell. The subroutine is as follows:

Sub RunCalculateStuff()
   Dim initialRange As Range
   Set initialRange = Application.InputBox("Please select the cells in the first column you would like to use...", Type:=8)

   Dim finalRange As Range
   Dim i As Long
   i = 0
   For Each initialCell In initialRange
      If i = 0 Then
         Set finalRange = ActiveSheet.Range(initialCell, initialCell.Offset(0, 4))
         i = i + 1
      Else
         Set finalRange = Application.Union(finalRange, ActiveSheet.Range(initialCell, initialCell.Offset(0, 4)))
         i = i + 1
      End If
   Next

   ActiveCell.Formula = "=CalculateStuff(" + finalRange.Address + ")"

   ActiveCell.AutoFill Destination:=ActiveSheet.Range(ActiveCell, ActiveCell.Offset(0, 4))
End Sub

The subroutine works by letting the user select the cells in the first column they would like to use, then loops through those cells and grabs the cells up to an offset of (0, 4) away and adds that range to an overall range. This range is then fed to this function and it goes.

Heres' where the #VALUE! error comes in... It only happens when the cells that I select are not sequential... By this I mean, if I select the range AA1:AA4 with the initial get box, it works just fine. If I select the range AA1, AA2, AA3, AA4 individually, it works just fine. But if I select the range AA1, AA3, I get the #VALUE! Error. I get the feeling it has something to do with skipping rows, but I really don't understand why since I'm doing a Union into its own range. Plus, it fails when I just try to sum the first column of the range in the very first calculation, then it fails in the rest as well. Screenshots below for what I mean.

Working Range:

Broken Range:

Thank you in advance for your help! I really appreciate it.

Answer 1

This seemed to work for me. Posting in case you did not have any luck, or if you need inspiration. Note that I didn't do any error checking to see if all the elements of your input are Ranges.

Function CalculateStuff2(ParamArray Rngs()) As Double

    Dim i As Integer
    Dim col As Long
    Dim tmpRng As Range
    Dim tmpDbl As Double
    Dim divisor As Double
    Dim IsCase2 As Boolean
    Dim numRows As Long, r As Long

    For i = LBound(Rngs()) To UBound(Rngs())
        Set tmpRng = Rngs(i)
        col = Application.Caller.Column - tmpRng.Column
        numRows = tmpRng.Rows.Count

        Select Case col
            Case 0
                tmpDbl = tmpDbl + WorksheetFunction.Sum(tmpRng.Columns(1))
            Case 1
                tmpDbl = tmpDbl + WorksheetFunction.SumProduct(tmpRng.Columns(1), tmpRng.Columns(2))
            Case 2
                IsCase2 = True
                tmpDbl = tmpDbl + WorksheetFunction.SumProduct(tmpRng.Columns(1), tmpRng.Columns(3))
                divisor = divisor + WorksheetFunction.Sum(tmpRng.Columns(1))
            Case 3
                tmpDbl = tmpDbl + WorksheetFunction.SumSq(tmpRng.Columns(4))
            Case 4
                For r = 1 To numRows
                    tmpDbl = tmpDbl + tmpRng(r, 1) * WorksheetFunction.SumSq(tmpRng(r, 3), tmpRng(r, 5))
                Next r
        End Select
    Next i

    If IsCase2 Then
        CalculateStuff2 = tmpDbl / divisor
    Else
        CalculateStuff2 = tmpDbl
    End If
End Function