AverageIF in VBA with dynamic range

Question

I have a VBA Code that creates a pivot table and then finds the average based on the dynamic range (as the size of the pivot table is always changing)

I would like to use AVERAGEIF function instead of average so that I can average all values where ">0". The problem I have is that Excel does not allow quotes inside of quotes, so I can't put the criteria ">0" in the function. My current code is:

Sub BuildLaborTable()
'
'This procedure builds the labor table and finds the average of the rows

ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("Pivot ZP2P").PivotFields("Notif Service product")
    .Orientation = xlRowField
    .Position = 1
End With

ActiveSheet.PivotTables("Pivot ZP2P").AddDataField ActiveSheet.PivotTables( _
    "Pivot ZP2P").PivotFields("Actual Qty"), "Sum of Actual Qty", xlSum

With ActiveSheet.PivotTables("Pivot ZP2P").PivotFields("Notifctn")
    .Orientation = xlColumnField
    .Position = 1
End With                     

Range("A6").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Average"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "Median"
ActiveCell.Offset(1, -1).Select
ActiveCell.FormulaR1C1 = "=AVERAGE(RC2:RC[-2])"

End Sub

I would like to use the formula AVERAGEIF(RC2:RC[-2],">0") , but the issue is this requires the quotes inside of quotes which is not allowed.

Why are quotes in quotes not allowed? – Tim Wilkinson Jun 13 '18 at 19:14 — Tim Wilkinson, Jun 13 '18 at 19:14
@TimWilkinson Because he said so, geez!! /s :) – dwirony Jun 13 '18 at 19:15 — dwirony, Jun 13 '18 at 19:15

score 2 · Answer 1 · answered Jun 13 '18 at 19:19

You can double up quotes to use within VBA. And as a side note you can put a lot of your lines together to avoid using select all the time

With Range("A6").End(xlToRight)
    .Offset(0, 1).Value = "Average"
    .Offset(0, 2).Value = "Median"
    .Offset(1, 1).FormulaR1C1 = "=AVERAGEIF(RC2:RC[-2],"">0"")"
End With

score 1 · Accepted Answer · answered Jun 13 '18 at 19:10

1

When using quotes in a formula within VBA, simply "double up":

ActiveCell.FormulaR1C1 = "=AVERAGEIF(RC2:RC[-2],"">0"")"

answered Jun 13 '18 at 19:10

BruceWayne

22,923
15
65
110

Yes! Thank you Bruce, I was unaware of the "">0"" double quotes in quotes in quotes as an option. This is perfect!! – ClaireLandis Jun 13 '18 at 19:21

AverageIF in VBA with dynamic range

2 Answers2