Sum portion of array without loop

[ElmerS]

Hi,

I do hope the attached picture will be sufficient in order to help me.
(I faced a problem to attach this small WB as an XLS).

I'm looking for some way to some the red cells without looping.

In this example the array was filled with A1:A10 values.
In the real situation the array gets its values from other source than a Worksheet Range.

As you can see I manged to transport the Array Values to F1:F10 and from here I could calculate the sum of F3:F8 but I do not want to use any helper columns.

As you can see the wrong! sum command, I have used ,presents an 'Error 9'.

Thanks, Elm

[shg]

Option Explicit

Sub x()
    Dim v As Variant
    
    With Columns(1)
        v = Range(.Cells(Range("C2").Value), .Cells(Range("D2").Value)).Value
    End With
    MsgBox WorksheetFunction.Sum(v)
End Sub

The important line is the first one. It would tell you that C2 and D2 in your code are undeclared variables.

[ElmerS]

Thanks, shg,

Unfortunately, you must have missed the crucial line which states: "In the real situation the array gets its values from "other source than a Worksheet Range".

Please Ignore Range A1:A10 and Range F1:F10 and try to find a solution from a point where the Array is already filled with those 10 values.

Elm

[DonkeyOte]

If you can't possibly iterate the Array (for whatever reason) then temporarily place your Array into a worksheet object so you can use the method already outlined by shg.

[ElmerS]

Thanks, DonkeyOte,

As you can see I manged to place all 10 values into range F1:F10 and from there I would have known how to sum a part of a that range.

My intention is NOT to go through and/or use a Worksheet as an helper.

If there is no way to accomplish my request with "pure" VBA commands without relaying on Worksheets or Worksheets Objects that would be preferable.

However, as long as the solution will rum through something that is fully transparent to the user even if it needs a Worksheet Object - it will be fine - but as I'm not familiar with "worksheet objects" - please lighten up my eyes with a sample code.

Thanks, Elm

[DonkeyOte]

If you don't want to use a Worksheet to store the Array temporarily (such that you can sum quickly as per any standard range) then you will AFAIK need to iterate the Array... and I stress again, AFAIK...

[shg]

Option Explicit

Sub x()
    Dim v1 As Variant
    Dim iBeg As Long
    Dim iEnd As Long
    
    ' however v1 gets initialized ...
    v1 = Range("A1:A10").Value
    iBeg = Range("C2").Value
    iEnd = Range("D2").Value
    
    ' then ...
    With Range("B1").Resize(UBound(v1)) ' or any other convenient place
        .Value = v1
        MsgBox WorksheetFunction.Sum(Range(.Cells(iBeg), .Cells(iEnd)))
    End With
End Sub

[ElmerS]

Hi,

Who mentioned anything about a looping problem.

This is a part of a Technical High-school Exercise.

AFAIR - those Exercises have never been too easy...

Elm

[romperstomper]

This is a part of a Technical High-school Exercise.

Oh, I see. I'll leave you to it then.