Application.Averageifs

[all047]

Hello,

Is there any special way of using averageifs in VBA - I know I can do it as part of a formula, but it wouldn't work in this situation.

It's always returning a value error when running without the iserror check, even though there is correct data as i tested with a formula.

Sub test3p2()

Set ws3 = ThisWorkbook.Worksheets("Test 3")
Set ws2 = ThisWorkbook.Worksheets("Test 2")
Dim i As Integer
Dim x As Integer


i = 1
x = WorksheetFunction.CountA(Columns("A:A"))


Do Until i = x


    m = ws3.Range("A" & 1 + i)
    Dow = ws3.Range("G2")
    sp = ws3.Range("H2")
    ep = ws3.Range("I2")


  
   Average = Application.AverageIfs(ws2.Range("D2:D57217"), ws2.Range("A:A"), m, ws2.Range("C2:C57217"), ">=" & sp, ws2.Range("C2:C57217"), "<=" & ep)
     If IsError(Average) Then
        Average = 0
     End If
   ws3.Range("B" & 1 + i) = Average
 i = i + 1


Loop




End Sub

Any help would be great

 

[Marcelo Branco]

In AVERAGEIFS all the ranges must have the same size. Your formula has RANGE( "A:A") that refers to an entire column while the other ranges refer to rows 2:57217. Try replacing Range("A:A") by Range("A2:A57217").

By the way, setting the variables Dow, sp and ep could (should) be before the loop.

M.

 

[all047]

Thank you so much! Can't believe it was so simple, been super annoyed about it for hours! Many thanks

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

Other Suggestions.
1. DIm i and x as Long instead of Integer. Integer has a maximum of 32767. If you have more rows an overflow may occur.

2. You can do the same thing without a loop using something like this

'get the last row with data
With ws2
  lastRow = .Cells(.rows.count, "A").End(xlup).Row
End With

With ws2.Range("B2:B" & lastRow)
    .Formula = "Your formula here"
   .value = .value
End with

Hope this helps

M.