AverageIF in VBA

Hey everyone,

first a big thanks to this amazing community. I have been learning from you for years <3

I´m trying to get the average of a column without the zeros being recognized in VBA.
The code for the regular average is working just fine, but when I exchange the average formula with AverageIf, it does not work in VBA.

Solution with 0 recognized in VBA that works:

Sub MittelwertDmit0()
Range("D34").Formula = "=Average(D1:D33)"

End Sub



If I change the average formula with one of the following two, which is what I need, it does not work anymore. Even though in Excel both of them work just fine, just not in VBA:
1.=AverageIf(D2:D33;">0")

2. =Average(If(D2:D33<>0;D2:D33))


Can someone help?

Thanks already in advance

Instead of:

Please Login or Register  to view this content.

Try:

Please Login or Register  to view this content.

With an averageif you need (Range, Criteria, Average range) to get the result, you may need to change my "," to a ";" for your system.

Hope this helps

Variant:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Hey CheeseSandwich,

thanks so much for your quick response.

The new Code is:

Sub AverageD()
Range("D34").Formula = "=AVERAGEIF(D2:D33;">0";D2:D33)"

End Sub

Unfortunately now I get the same Error Message that I also got before:
Compile Error - Expected: End of statement

If I exclude the "" around ">0" the compile error is gone but then of course the formula itself does not work anymore.

Any ideas for a VBA newbie?

Response to mjr veverka :

Thanks for the input. Unfortuntly crzay things happen now :D
My data in column D got now exchanged with the numbers 1-9 in the rows 1-9

SOLUTION:

With a little adaptation the solution from mjr veverka works.

Thanks so much for the uick help everyone.


Sub Mittelwert()

Dim r As Long, rr As Long, c As Long
Dim wrng As String


r = 2
rr = Cells(Rows.Count, "D").End(xlUp).Row
wrng = "$D$" & r & ":$D$" & rr
Range("D34").Formula = "=AVERAGEIF(" & wrng & ","">0""," & wrng & ")"

End Sub

Originally Posted by Philip_

Hey CheeseSandwich,

thanks so much for your quick response.

The new Code is:

Sub AverageD()
Range("D34").Formula = "=AVERAGEIF(D2:D33;">0";D2:D33)"

End Sub

Unfortunately now I get the same Error Message that I also got before:
Compile Error - Expected: End of statement

If I exclude the "" around ">0" the compile error is gone but then of course the formula itself does not work anymore.

Any ideas for a VBA newbie?

This is because you had quotations inside quotations. They need to be double quoted as per AB33s comment.