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]

See if something like this would be suitable

Dim myRanges(1 To 4) As Variant

Set myRanges(1) = ws.Range("B1:B" & (f - 1))
Set myRanges(2) = ws.Range("B" & f & ":B" & (g - 1))
Set myRanges(3) = Range("B" & g & ":B" & (h - 1))
Set myRanges(4) = ws.Range("B" & h & ":B" & lr)

For i = LBound(myRanges) To UBound(myRanges)
  x = WorksheetFunction.Average(myRanges(i))
Next i

BTW, if you want to highlight some of your code with colour, bold etc as you tried above, you need to use the 'rich' tags, not the 'vba' tags

 

[steve the fish]

Cant see too much wrong with what you have. You could get that error if there are no values in a particular range you are trying to average. What is the description of the error?

 

[farmerscott]

Dim myRanges(1 To 4) As Variant

Set myRanges(1) = ws.Range("B1:B" & (f - 1))
Set myRanges(2) = ws.Range("B" & f & ":B" & (g - 1))
Set myRanges(3) = Range("B" & g & ":B" & (h - 1))
Set myRanges(4) = ws.Range("B" & h & ":B" & lr)

For i = LBound(myRanges) To UBound(myRanges)
x = WorksheetFunction.Average(myRanges(i))
Next i

Hi Peter,

the code worked. Appreciated. And thanks for the tip on the forum formatting.

Any quick thoughts on why my approach was not successful?

FarmerScott

 

[farmerscott]

Cant see too much wrong with what you have. You could get that error if there are no values in a particular range you are trying to average. What is the description of the error?

Hi Steve,

I was getting a "Runtime error 1004" coming up. The ranges had valid data.

I saw this looping (via the Choose function) on a post not more than a week ago. Thought I would give it a go. The idea of a range of ranges is a bit foreign to me. The only other thing I could find was a post by Andrew Polson (on MrExcel) where he used a loop using "For each area in area.ranges".

Thanks for the help.

cheers

FarmerScott

 

[Peter_SSs]

What is the description of the error?

I was getting a "Runtime error 1004" coming up.

We already knew that from post #1.
steve the fish is asking for the detail of the error message. Examples:

Are you saying that my code worked and yours didn't for the identical data and values of f, g, h and lr?

If so, we may need to see the entire code (and sample data with XL2BB) to see if we can reproduce the error to investigate further.

 

[farmerscott]

Hi Peter,

thanks for your interest...... and I will try to give you a brief answer.

1. yes your code worked on the same data in the same worksheet, so thanks.
2. the variables of f, g, h, and lr are all giving the right values.

Some points to the code-
1. in Col A there are the years 1886 to 2019. In col B I have yearly rainfall.
2. the purpose of the code is to pick years (1900, 1950 and 2000 are arbitary points) that show cycles within the long term rainfall.
3. the code breaks these periods into ranges where I want to get average rainfall and SD on each set of numbers (rng2 to rng5 within my code).
4. the charting part of the code works.

Sub Rainfall_per_period()
' get data for chart.
'creates chart over specific time (years) periods.

Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As String
Dim f As String
Dim g As Integer
Dim h As Integer
Dim i As Integer
Dim j As Integer

Dim x As Integer
Dim y As Integer

Dim rng As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim rng5 As Range
Dim rng6 As Range
Dim cht As ChartObject
Dim ChrtSrs1 As Series
Dim lr As Long
Dim lr2 As Long
Dim ws As Worksheet

Dim xvalues As Variant
Dim yvalues As Variant

Set ws = Worksheets("Decade")
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
lr2 = ws.Cells(Rows.Count, "AH").End(xlUp).Row

Set rng = ws.Range("A1:A" & lr)

'get data.
a = WorksheetFunction.Min(rng)
b = 1900
c = 1950
d = 2000
f = WorksheetFunction.Max(rng)

'paste year ranges to sheet.
ws.Range("S1").Value = a & " to " & (b - 1)
ws.Range("S2").Value = b & " to " & (c - 1)
ws.Range("S3").Value = c & " to " & (d - 1)
ws.Range("S4").Value = d & " to " & f
ws.Columns("S").AutoFit

'get row numbers of years (a-f)
g = Application.Match(a, rng, 1)
h = Application.Match(b, rng, 1)
i = Application.Match(c, rng, 1)
j = Application.Match(d, rng, 1)

'create ranges.
Set rng2 = ws.Range("B1:B" & (h - 1))
Set rng3 = ws.Range("B" & h & ":B" & (i - 1))
Set rng4 = Range("B" & i & ":B" & (j - 1))
Set rng5 = ws.Range("B" & j & ":B" & lr)

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

    x = WorksheetFunction.Average(rng) 'erroring here. "Unable to get the average property of the worksheet function class"
    y = WorksheetFunction.StDev(rng) 'erroring here.  "Unable to get the average property of the worksheet function class"
    ws.Range("T" & i).Value = x
    ws.Range("U" & i).Value = y
Next i


'create chart for periods.
'Your data range for the chart
xvalues = Worksheets("Decade").Range("S1:S" & lr2)
yvalues = Worksheets("Decade").Range("T1:T" & lr2)
  

'Create a chart
  Set cht = Worksheets("Decade").ChartObjects.Add(Left:=100, Width:=100, Top:=100, Height:=100)


With cht
      .Top = Range("V2").Top
     .Left = Range("V2").Left
     .Width = Range("J2:S2").Width
    .Height = Range("J2:J22").Height
    .Name = "Rainfall per Period"
End With


'Determine the chart type and attributes

  With cht.Chart
  .ChartType = xlColumnClustered
  .HasLegend = False
  .HasTitle = True
  .ChartTitle.Text = Sheets(2).Name & " " & "Average Rainfall per Period (mm)"
 

  End With
  
 'Determine the chart series and colour
  Set ChrtSrs1 = cht.Chart.SeriesCollection.NewSeries
  With ChrtSrs1
        
        .Values = yvalues
        .xvalues = xvalues
        .ApplyDataLabels
        .Interior.Color = vbBlue
         
        
        
    End With
   With cht.Chart.ChartGroups(1)
   .GapWidth = 40
   
   End With
 
End Sub

I modified the code to-

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

    ws.Range("T" & i).Value = x
   
Next i

From preliminary testing, it look to be working. I am suspecting that in the first code the rng variable within the loop was not taking on the settings of rng2 - rng 5. Within the worksheet ("Decade") the original rng

Set rng = ws.Range("A1:A" & lr)

was being deleted. Which gave me a little clue to what was happening.

That is where I am up too.

Cheers.

 

[farmerscott]

Peter,

further to my response, I suspect that the line-

rng = WorksheetFunction.Choose(i, rng2, rng3, rng4, rng5)

was literally being evaluated as a range.

thx.

 

[Peter_SSs]

You have declared rng as Range. To set the range you need to use the keyword Set

Try making this change in the first code above

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

    x = WorksheetFunction.Average(rng) 'erroring here. "Unable to get the average property of the worksheet function class"
    y = WorksheetFunction.StDev(rng) 'erroring here.  "Unable to get the average property of the worksheet function class"
    ws.Range("T" & i).Value = x
    ws.Range("U" & i).Value = y
Next i

 

[farmerscott]

Hi Peter,

that did the trick.

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

Anyway all working.

Thanks,

FarmerScott