VBA- Loop thru a range of ranges to get an average.

[farmerscott]

Hi Everybody,

As per the code below, I have 4 ranges. I would like to find a way to loop thru them, so I can get an average of each.

The code is erroring- as marked.

Set rng2 = ws.Range("B1:B" & (f - 1))
Set rng3 = ws.Range("B" & f & ":B" & (g - 1))
Set rng4 = ws.Range("B" & g & ":B" & (h - 1))
Set rng5 = ws.Range("B" & h & ":B" & lr)


For i = 1 To 4 Step 1
rng = WorksheetFunction.Choose(i, rng2, rng3, rng4, rng5)

x = WorksheetFunction.Average(rng) [COLOR=rgb(184, 49, 47)]'erroring on this line. "Runtime error 1004"[/COLOR]
Next i

1. This is part of a bigger code.
2. the variables within the "setting" of the ranges above are providing the correct results.

thanks for the help,

FS.

 

[Peter_SSs]

Not sure why we have to "Reset" as rng, rng2, etc are already "Set".

Because if you don't again use 'Set' to define the range, the code will try to put a value in the already 'set' range. It would be like this which would put "abc" into the range A1:A2

Dim rng As Range

Set rng = Range("A1:A2")
rng = "abc"

Further, the 'already set range' rng was A1:A & lr whereas when you come to use rng again in that loop, you want rng to be one of the sub-ranges of that original rng.

 

[farmerscott]

Understood.

Thanks Peter.