VBA Performing calculations on discontinuous cell addresses

[nort_amgis]

Hi, I have some working code that I'd like to expand on. Basically I'll have a bunch of individual cell addresses "AB10", "AD10", ... "CZ10", and so on that i'd like to compile some basic statistics on. But when I try to get the values in a way that's easy to work with, like in this example:

Arrays And Ranges In VBA

I get errors left and right and can't really get a solid start on something that should be pretty simple. The size of the array will be variable, so the code will ideally be dynamic. Or if I could just get away with using the application functions, I'd be happy with that since the data is usually only a few hundred lines.

If it's not clear what I'm asking/trying to do, please just point me in the direction of a working examples where the average, min, max, etc are calculated from a range of discontinuous (not contiguous) cells or a 2 dimensional array of data.

Or just post some pseudo code that you're confident will work, really anything helps, thanks

 

[Marcelo Branco]

See if this very simple example helps. Suppose you want to calculate the AVG and SUM of values in Sheet1 A2, C2 and F2 (non-contiguous cells on the same row, for example).

Something like...
Sheet1

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

Value1​

[/TD]
[TD][/TD]
[TD]

Value2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Value3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

10,4​

[/TD]
[TD][/TD]
[TD]

12,8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD]

20,1​

[/TD]
[/TR]
</tbody>[/TABLE]


Try

Sub aTest()
    Dim rngRange As Range, dblAverage As Double, dblSum As Double
    
    Set rngRange = Sheets("Sheet1").Range("A2,C2,F2")
    'Calc Average
    dblAverage = Application.Average(rngRange)
    MsgBox dblAverage
    'Calc Sum
    dblSum = Application.Sum(rngRange)
    MsgBox dblSum
End Sub

or using a Variant Array

Sub bTest()
    'Using a variant array
    Dim vRange As Variant, dblAverage As Double, dblSum As Double
    
    'Pass the values of columns [COLOR=#0000ff]1, 3, 6[/COLOR] of the range A2:F2 to a variant array
    vRange = Application.Index(Sheets("Sheet1").Range("A2:F2").Value, 1, Array([COLOR=#0000ff]1, 3, 6[/COLOR]))
    'Calc Average
    dblAverage = Application.Average(vRange)
    MsgBox dblAverage
    'Calc Sum
    dblSum = Application.Sum(vRange)
    MsgBox dblSum
End Sub

Hope this helps

M.

 

[Ruddles]

I suspect Marcelo's suggestion is better than mine, but an alternative approach would be to copy all the cells into a contiguous area of a clean new worksheet and do the calculations there.

 

[nort_amgis]

I suspect Marcelo's suggestion is better than mine, but an alternative approach would be to copy all the cells into a contiguous area of a clean new worksheet and do the calculations there.

That was one way I was doing things, but apparently you can go from Range to Array fairly easily in VBA and an array doesn't need need a sheet to write to, just upper bounds and ReDimming when necessary.

Marcelo, that works, thanks! Had to take the comma out though, heh. Looks like I was referencing the range incorrectly like "A2", "C2", "F2", which is wrong. I'm going to play around with this for a bit, I'll reply here if anything else comes up.