Sum portion of array without loop

**ElmerS** · 06-24-2009, 10:17 AM

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** · 06-24-2009, 10:40 AM

Please Login or Register  to view this content.

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

**ElmerS** · 06-24-2009, 11:14 AM

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** · 06-24-2009, 11:18 AM

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** · 06-24-2009, 11:29 AM

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** · 06-24-2009, 11:46 AM

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** · 06-24-2009, 11:48 AM

Please Login or Register  to view this content.

**ElmerS** · 06-24-2009, 11:56 AM

Thanks,

Well..., I will "rephrase" the question although it seems to be the same "problem".

Assume I have a 10 cells Array.

How can I copy those red values (from cell 3-8 into cells 1-6 in a second Array - WITHOUT looping and without going through a Worksheet Range.

Elm

**romperstomper** · 06-24-2009, 12:07 PM

What exactly is the problem with looping?

**ElmerS** · 06-24-2009, 12:11 PM

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

**shg** · 06-24-2009, 12:12 PM

You could familarize yourself with VBA's SafeArray data structure, use VarPtr to get the base address of the array, compute the offset to the data of interest, and use the CopyMemory API to copy the data to a new array.

Happy hunting.