AverageIF in VBA with dynamic range [duplicate]

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
– Tim Wilkinson

2018-06-13 19:14:27 +00:00
Commented Jun 13, 2018 at 19:14 — Tim Wilkinson
– Tim Wilkinson, Commented Jun 13, 2018 at 19:14
@TimWilkinson Because he said so, geez!! /s :)

dwirony
– dwirony

2018-06-13 19:15:19 +00:00
Commented Jun 13, 2018 at 19:15 — dwirony
– dwirony, Commented Jun 13, 2018 at 19:15

Tim Wilkinson · Accepted Answer · 2018-06-13 19:19:53Z

2

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

answered Jun 13, 2018 at 19:19

Tim Wilkinson

3,79111 gold badges38 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BruceWayne · Accepted Answer · 2018-06-13 19:10:45Z

1

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

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

answered Jun 13, 2018 at 19:10

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

1 Comment

ClaireLandis Over a year ago

Yes! Thank you Bruce, I was unaware of the "">0"" double quotes in quotes in quotes as an option. This is perfect!!

Collectives™ on Stack Overflow

AverageIF in VBA with dynamic range [duplicate]

2 Answers 2

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Linked

Related